Insert file data in MySQL using Python

[tigrum]

Hello,

I am currenty using MySQL 5.1 community server and trying to import the data of the comma delimited text file into the table using python 2.6 scripts. I have installed Mysqldb 1.2.2.

follwoing is my script:

import MySQLdb, csv, sys
conn = MySQLdb.connect (host = "localhost",user = "usr", passwd = "pass",db = "databasename")
c = conn.cursor()
csv_data=csv.reader(file("a.txt"))
for row in csv_data:
	print row
	c.execute("INSERT INTO a (first, last) VALUES (%s, %s), row")
c.commit()
c.close()

the contents of the text file eg. :
-----------------
John,Smith
Danie,Thomas
Ronald,Rey
--------------------

When I execute the statement I get the following error:
------------------------------------------------------------------------------------
C:\Python26\lib\site-packages\MySQLdb\__init__.py:34: DeprecationWarning: the sets module is deprecate
d
from sets import ImmutableSet

Traceback (most recent call last):
File "e:\Scripts\test.py", line 10, in <module>
c.execute("INSERT INTO a (first, last) VALUES (%s, %s), row")
File "C:\Python26\lib\site-packages\MySQLdb\cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "C:\Python26\lib\site-packages\MySQLdb\connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual tha
t corresponds to your MySQL server version for the right syntax to use near '%s, %s), row' at line 1")
------------------------------------------------------------------------------------

Any kind of help to get me going will be greatly appreciated.

[jice]

Hello,

c.execute("INSERT INTO a (first, last) VALUES (%s, %s), row")

Your "row" is inside the sql statement as a string !
You can do

c.execute("INSERT INTO a (first, last) VALUES (%s, %s)" % row)

[tigrum]

Somehow I am not able to update a specific field with all the vaues in the file. For eg:

Before the update my table contents are:
+-------+-------+
| first | last |
+-------+-------+
| Sara | Jones |
| Terry | Burns |
| Filiz | Khan |
+-------+-------+

When I do the update using the following code,
-------------------------------------------------------------------------------------------------------------------------------------------

import MySQLdb, csv, sys
conn = MySQLdb.connect (host = "localhost",user = "usr", passwd = "pass",db = "db")

c = conn.cursor()
csv_data=csv.reader(file("b.txt"))
for row in csv_data:
    print row
    c.execute("UPDATE a SET last = %s", row)
#c.commit()
c.close()

---------------------------------------------------------------------------------------------------------------------------------------------

The table contents get updated with the last content of the input file . for eg
+-------+------+
| first | last |
+-------+------+
| Sara | c |
| Terry | c |
| Filiz | c |
+-------+------+

the contents of the b.txt file:

a
b
c

what is the wrong thing that I am doing here?

[Stefano Mtangoo]

Executing SQL that way isn't good. Here is a sample function that will illustrate how to do it better. It is for sqlite3 imported as sqlite. If you have problems in implementing it just post back

def createtb(self):
        query1 = "DROP TABLE IF EXISTS bible"
        query2 = """CREATE TABLE IF NOT EXISTS bible(
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "book" VARCHAR(32) NOT NULL,
  "cap" INTEGER(3) NOT NULL,
  "verse" INTEGER(3) NOT NULL,
  "line" VARCHAR(530) default NULL
)
"""
        self.cur.execute(query1)
        self.cur.execute(query2)

[Stefano Mtangoo]

Executing SQL that way isn't good. Here is a sample function that will illustrate how to do it better. It is for sqlite3 imported as sqlite. If you have problems in implementing it just post back

def createtb(self):
        query1 = "DROP TABLE IF EXISTS bible"
        query2 = """CREATE TABLE IF NOT EXISTS bible(
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "book" VARCHAR(32) NOT NULL,
  "cap" INTEGER(3) NOT NULL,
  "verse" INTEGER(3) NOT NULL,
  "line" VARCHAR(530) default NULL
)
"""
        self.cur.execute(query1)
        self.cur.execute(query2)

[jlm699]

c.execute("UPDATE a SET last = %s", row)

what is the wrong thing that I am doing here?

I believe you forgot to replace the comma (,) with a percentage sign (%). Shouldn't the above code be:

c.execute("UPDATE a SET last = %s" % row)

?

HTH

[octav1]

maybe you should have
c.execute("INSERT INTO a (first, last) VALUES (%s, %s)" % row)

[octav1]

make one more correction to convert from list to tuple:
c.execute("INSERT INTO a(first, last) VALUES ('%s', %s)" % tuple(row))
also, the commit should be
conn.commit()