Re: ERROR when inserting csv values into sql table

Hi Mark,

It didn’t work, and I made some minor changes to my code as well.

Thanks,

Cravan

 

From: Mark Wallace <[email protected]>
Date: Thursday, 13 June 2019 at 7:31 PM
To: "[email protected]" <[email protected]>
Cc: cool kid <[email protected]>
Subject: Re: ERROR when inserting csv values into sql table

 

As Anthony E. Greene already mentioned yesterday (06/12/19 @ 08:07am EDT) in a comment on this thread, you are data typing imdbRating as INTEGER, when it should be NUMERIC.

 

Try again with this DDL:

 

CREATE TABLE movies (

      "title" SERIAL PRIMARY KEY,

      "year" INTEGER NOT NULL,

      "runtime" INTEGER NOT NULL,

      "imdbID" VARCHAR NOT NULL,

      "imdbRating” NUMERIC NOT NULL

  );

 

 

Mark



On Jun 13, 2019, at 03:40, Cravan <[email protected]> wrote:

 

Hi guys, 

Have edited the code, but Im still getting an error. I have already edited my apostrophes and spacing and looked up examples from google to try and resolve my error to no avail. I also ensured that i defined DATABASE_URL properly. Sorry for the long traceback error at the end :P Please note that my csv values are stored in lists in each cell. They are arranged in a single column such as

The Lego Movie;2014;100;tt1490017;7.8  <—  The last value (7.8) is of type numeric, not integer.

This is my main code

import csv

import sys

import os

from sqlalchemy import Column, ForeignKey, Integer, String

from sqlalchemy import create_engine

from flask import Flask, render_template, request, session

from flask_sqlalchemy import SQLAlchemy

from flask_session import Session

 

engine = create_engine(os.getenv("DATABASE_URL")) # database engine object from SQLAlchemy that manages connections to the database,# DATABASE_URL is an environment variable that indicates where the database lives

 

def main():

    f = open("movies.csv","r")

    reader = csv.reader(f)

    for row in f: # loop gives each column a name

        vals = row.split(';')

        title = vals[0]

        year = vals[1]

        runtime = vals[2]

        imdbID = vals[3]

        imdbRating = vals[4]

        engine.execute('INSERT INTO movies("title","year","runtime","imdbID","imdbRating") VALUES ((title), (year), (runtime), (imdbID), (imdbRating))',

        {"title": title, "year": year, "runtime": runtime, "imdbID": imdbID, "imdbRating": imdbRating })

    engine.commit() # transactions are assumed, so close the transaction finished

if __name__ == "__main__":

    main()

 

SQL code:

CREATE TABLE movies (

      "title" SERIAL PRIMARY KEY,

      "year" INTEGER NOT NULL,

      "runtime" INTEGER NOT NULL,

      "imdbID" VARCHAR NOT NULL,

      "imdbRating" INTEGER NOT NULL

  );

New error code:

#############################

Traceback (most recent call last):

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/base.py", line 1244, in _execute_context

    cursor, statement, parameters, context

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/default.py", line 550, in do_execute

    cursor.execute(statement, parameters)

psycopg2.errors.UndefinedColumn: column "title" does not exist

LINE 1: ...,"year","runtime","imdbID","imdbRating") VALUES ((title), (y...

                                                             ^

HINT:  There is a column named "title" in table "movies", but it cannot be re

ferenced from this part of the query.

 

 

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "import.py", line 26, in <module>

    main()

  File "import.py", line 23, in main

    {"title": title, "year": year, "runtime": runtime, "imdbID": imdbID, "imd

bRating": imdbRating })

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/base.py", line 2166, in execute

    return connection.execute(statement, *multiparams, **params)

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/base.py", line 982, in execute

    return self._execute_text(object_, multiparams, params)

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/base.py", line 1155, in _execute_text

    parameters,

File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/base.py", line 1248, in _execute_context

    e, statement, parameters, cursor, context

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception

    util.raise_from_cause(sqlalchemy_exception, exc_info)

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/util/compat.py", line 128, in reraise

    raise value.with_traceback(tb)

  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/base.py", line 1244, in _execute_context

    cursor, statement, parameters, context

File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-

packages/sqlalchemy/engine/default.py", line 550, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "ti

tle" does not exist

LINE 1: ...,"year","runtime","imdbID","imdbRating") VALUES ((title), (y...

                                                             ^

HINT:  There is a column named "title" in table "movies", but it cannot be re

ferenced from this part of the query.

 

[SQL: INSERT INTO movies("title","year","runtime","imdbID","imdbRating") VALU

ES ((title), (year), (runtime), (imdbID), (imdbRating))]

[parameters: {'title': 'Title', 'year': 'Year', 'runtime': 'Runtime', 'imdbID

': 'imdbID', 'imdbRating': 'imdbRating\n'}]

(Background on this error at: http://sqlalche.me/e/f405)

Here’s a pic of the new table:

<image001.png>

Thanks,

Cravan

import csv
import sys
import os
import sqlalchemy
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy import create_engine
from flask import Flask, render_template, request, session
from flask_sqlalchemy import SQLAlchemy
from flask_session import Session

engine = create_engine(os.getenv("DATABASE_URL")) # database engine object from SQLAlchemy that manages connections to the database,# DATABASE_URL is an environment variable that indicates where the database lives

def main():
    f = open("movies.csv","r")
    reader = csv.reader(f, delimiter=';')
    for i, row in enumerate(reader): # loop gives each column a name
        if i == 0:
            continue
        title = row[0]
        year = int(row[1])
        runtime = int(row[2])
        imdbID = row[3]
        imdbRating = float(row[4])
        insert_statement = sqlalchemy.text("INSERT INTO movies(title, year, runtime, "imdbID", "imdbRating") VALUES (:title, :year, :runtime, :imdbID, :imdbRating)")
        engine.execute(insert_statement, title=title, year=year, runtime=runtime, imdbID=imdbID, imdbRating=imdbRating)
    engine.commit() # transactions are assumed, so close the transaction finished
if __name__ == "__main__":
    main()