I am using psycopg2 to read from one database table and insert these into another table. I get a format error when the value I am trying to insert has a space in it. The condensed code looks like:
cursor.execute("""INSERT INTO table (column1, column2)
VALUES ('start finish','now later')""")
I'm a newbie to psycopg2 but this seems like a silly limitation. Any ideas for a work-around?
I am using psycopg2 to read from one database table and insert these into another table. I get a format error when the value I am trying to insert has a space in it. The condensed code looks like:
cursor.execute("""INSERT INTO table (column1, column2)
VALUES ('start finish','now later')""")I'm a newbie to psycopg2 but this seems like a silly limitation. Any ideas for a work-around?
The syntax of most database languages requires quotes around string values with spaces so the proper way of doing the above is:
cursor.execute('INSERT INTO table (column1, column2)
VALUES ("start finish","now later")')While you can use a single or double quote within Python, databases are not so forgiving. If Jim699's post works it is because of the different use of quotation marks. If that doesn't work, try
SQL_str = 'INSERT INTO table (column1, column2) VALUES ("start finish","now later")'
cursor.execute(SQL_str)Oh yes, and also don't forget to commit your transactions (a requirement for psycopg2). So if you do something like:
# Connect to our database
my_db = psycopg2.connect( my_db_info_string )
# Create a cursor for our transactions
my_cur = my_db.cursor( ... )
# Perform our transaction (query)
my_cur.execute( my_query )
# Commit this transaction to the database
my_db.commit()We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.