using list to input data using psycoyg2 python (with missing data)

Question

Want to get data with some missing values from cvs into postgresql. I am happy to insert using strings. I would like a fast was to do this using list or something similer. I also need to check for missing values and substitute as I get an error other wise.

for w in csvReader:  
    if a ==0:  
       var = w  
       sql_table = 'CREATE TABLE TableName (%s)' % ','.join('%s VARCHAR(50)' % name for name in var)  
        dict_cur.execute(sql_table)  

    else:  
        colnum = 0  
        for col in w:  
            nms= col   
            print w, 'column'  
            #this is not correct 
            sql_insert = ('INSERT INTO TableName (%s) VALUES (%s)' % (','.join('%s' % name for name in var),','.join('%s' % nm for nm in nms)))  
            cursor.execute(sql_insert)  
            colnum += 1  

        print w  
    a = a + 1

error Traceback (most recent call last): File "/home/matthew/workspace/dfdsggd/try_db_thing.py", line 41, in cursor.execute(sql_insert) psycopg2.ProgrammingError: syntax error at or near "." LINE 1: ...ORDER_ADM,MONTH_ADM,DAY_ADM,YEAR_ADM) VALUES (2,6,7,.,3,5,7) ^

some example from you csv and structure of table would be nice if you want a clear answer — kriss, Dec 05 '10 at 22:31

kriss · Answer 1 · 2010-12-05T22:40:01.570

0

Looking at the error message, it just looks like you are trying to insert a '.' just read from csv in your database in the field ORDER_ADM without putting it between quotes.

Also the three fileds DAY_ADM, MONTH_ADM, YEAR_ADM suggests that you probably should merge them into a date and put them in the same field of the database. But it means thinking somehow about structure of database (not making everything VARCHAR). Also creating table in the loop that reads data, even at first iteration is quite unusual.

edited Dec 05 '10 at 22:40

answered Dec 05 '10 at 22:32

kriss

23,497
17
97
116

Yes I agree on the loop but that is what I found. – user531525 Dec 06 '10 at 00:05
As I don't know exactly the data type to start with I was thinking about just getting the data in then changing it in Postgres. I can read in the data types using regex and auto change. – user531525 Dec 06 '10 at 00:06
This is really sort of an example. Psycopg2 makes things very hard to do because there are no prepared statements. – user531525 Dec 06 '10 at 00:09
I understand this is just an example. Isn't there ? http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html – kriss Dec 06 '10 at 00:34
My understanding is that Psycopg2 does not have prepared statements. There is something that works with python 3 and postgres that does but I can not use python 3 for most the stuff I am doing. – user531525 Dec 06 '10 at 02:53
I found some clarification in http://stackoverflow.com/questions/1947750/does-python-support-mysql-prepared-statements. You can access to prepared statement of underlying database at SQL level, but it does not protect you from SQL Injection as it does using PHP. I believe some ORM like Alchemy manage prepared statement, you may want to have at look at them. What aspect of prepared statement are you missing ? – kriss Dec 07 '10 at 06:57

using list to input data using psycoyg2 python (with missing data)

1 Answers1