TypeError: not all arguments converted during string formatting

You did not include the database scheme.
Thus I can only give generic advice and hope, that I guessed the column names correctly.


You can refer to the mysql syntax for example at http://dev.mysql.com/doc/refman/5.1/en/insert.html

normally an insert statement in mysql should look like:

'INSERT INTO table (column_name1 , column_name2, . . . )
VALUES  (column_value1, column_value2);

you should write herefore someting like
'INSERT INTO ptrecords  (Age, Gender)  VALUES (23, 'F');

or in python

cur.execute('INSERT INTO ptrecords  (Age, Gender)  VALUES  (%s, %s)', (23, 'F'))


Attached code shows how the SQL statement should look like
However I was too 'lazy' to type all column names, so my code just populates Age and Gender


As I don't have your database to test the code i might potentially have some typos in the code.

#get transferred information
            conn = lite.connect("PermPatRecord.sqlite")
            mysql_conn = MySQLdb.connect (host = 'localhost', user = 'root' ,passwd = '', db = 'transferdb')
            c = conn.cursor()
            c.execute('select * from ptrecords')
            for rows in c:
                Actual_Incident_number=(str(rows[0]))           
                Incident_number = (str(rows[1]))
                Age= (str(rows[4]))
                Gender = (str(rows[5]))
                Zip = (str(rows[8]))
                Ailment = (str(rows[10]))
                Treatment =(str( rows[11]))
                Initial_contact= (str(rows[13]))
                Hospital = (str(rows[14]))
                Inservice= (str(rows[16]))
                Destination= (str(rows[15]))
                Sys =(str(rows[19]))
                Dia=(str(rows[20]))
                Pulse=(str(rows[21]))
                Resp=(str(rows[22]))
                Weather=(str(rows[23]))
                Temp=(str(rows[24]))
                Humid=(str(rows[25]))
                Wind=(str(rows[26]))
                TimeStamp=(str(rows[27]))
                                        
                #connect = lite.connect('backup.sqlite')
                cur = mysql_conn.cursor()
                cur.execute('INSERT INTO ptrecords  
                      (Age, Gender)
                      VALUES 
                      (%s, %s)',
                      (Age, Gender));

Select allOpen in new window

I changed the code ut now Im getting a new error

db = MySQLdb.connect(host="localhost", user="root", passwd="",db="transferdb")
		cursor = db.cursor()
		
		#cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",(name, species))
		cursor.execute("INSERT INTO ptrecords (Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp) VALUES (%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s,%s, %s,%s, %s)",(Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp))
		
		db.commit()
		cursor.close()
		db.close()

Select allOpen in new window

Exception in thread Thread-4:
Traceback (most recent call last):
  File "C:\Python26\lib\threading.py", line 532, in __bootstrap_inner
    self.run()
  File "C:\Python26\sign\OCTOBER29_mysqldevlopment.py", line 6884, in run
    cursor.execute("INSERT INTO ptrecords (Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp) VALUES (%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s,%s, %s,%s, %s)",(Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp))
  File "C:\Python26\lib\site-packages\MySQLdb\cursors.py", line 158, in execute
    query = query % db.literal(args)
TypeError: not enough arguments for format string

Select allOpen in new window

   

I found the error here ... now im getting a whole new error ... will keep you updated

You have one '%s' too much.

I reformatted your querystring and you can see, that there is one '%s' more than arguments.

INSERT INTO ptrecords (Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp) 
VALUES                (%s,                     %s, %s,    %s, %s,     %s,       %s,             %s,      %s,         %s,       %s, %s, %s,   %s,  %s,      %s,  %s,   %s,  %s,      %s)",
                      (Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp))

Select allOpen in new window

I found that but now Im getting ... but it cleaerly exist ...I'm using sql manager 2011 and looking at it ... I've checked everything (spelling , cap , everything)... IDK

Exception in thread Thread-4:
Traceback (most recent call last):
  File "C:\Python26\lib\threading.py", line 532, in __bootstrap_inner
    self.run()
  File "C:\Python26\sign\OCTOBER29_mysqldevlopment.py", line 6884, in run
    cursor.execute("INSERT INTO ptrecords (Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp) VALUES ( %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s,%s, %s,%s, %s)",(Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp));
  File "C:\Python26\lib\site-packages\MySQLdb\cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python26\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'transferdb.ptrecords' doesn't exist")

Select allOpen in new window

Could you please start the mysql executable by hand with following command and tell me what you see?


mysql -h localhost -u root -p transfer
(You will be prompted for the password, which should be the same as in your python script)

You should get no error if not, then please tell me, which one.

Type then in the mysql prompt.

show tables;

Do you see the table 'ptrecords' in the list ? (Please Note, that names are case sensitive, so don't mix
upper / lower cacse letters)

If yes, then type

describe ptrecords;

Do you get an error or some output.

sorry for my newnes but are you talking about the MYSQL5.5 Command Line Client ?

Yes,

I'm talking about the command line client.

I think it exists also under windows, though I used in only with Linux.

It should be called mysql.exe

This is the error I get ... but I can see my table in sql manager 2011... any suggestions ?

_mysql_exceptions.ProgrammingError: (1146, "Table 'transferdb.ptrecords' doesn't exist")
File "C:\Python26\gelinda.py", line 23, in <module>
  cur.execute('describe %s' % table)
File "C:\Python26\Lib\site-packages\MySQLdb\cursors.py", line 173, in execute
  self.errorhandler(self, exc, value)
File "C:\Python26\Lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
  raise errorclass, errorvalue

Select allOpen in new window

I created a new table 'newtable' and your script gave me

my databases
('information_schema',)
('db56a',)
('mysql',)
('new_db',)
('performance_schema',)
('test',)
('transferdb',)

my tables
(' ptrecords',)
('newtable',)

 info about table newtable
('Active_Incident_number', 'int(11)', 'NO', 'PRI', None, '')
(' Age', 'int(11)', 'YES', '', None, '')
('Gender', 'varchar(20)', 'YES', '', None, '')
('Zip', 'int(11)', 'YES', '', None, '')
('Ailment', 'varchar(20)', 'YES', '', None, '')
('Treatment', 'varchar(20)', 'YES', '', None, '')
('Initial_contact', 'varchar(20)', 'YES', '', None, '')
('Hospital', 'varchar(20)', 'YES', '', None, '')
('Destination', 'varchar(20)', 'YES', '', None, '')
('Inservice', 'varchar(20)', 'YES', '', None, '')
('Sys', 'int(11)', 'YES', '', None, '')
('Dia', 'int(11)', 'YES', '', None, '')
('Pulse', 'int(11)', 'YES', '', None, '')
('Resp', 'int(11)', 'YES', '', None, '')
('Weather', 'varchar(20)', 'YES', '', None, '')
('Temp', 'varchar(20)', 'YES', '', None, '')
('Humid', 'varchar(20)', 'YES', '', None, '')
('Wind', 'varchar(20)', 'YES', '', None, '')
('TimeStamp', 'varchar(20)', 'YES', '', None, '')

Select allOpen in new window

but now I get :

Traceback (most recent call last):
  File "C:\Python26\lib\threading.py", line 532, in __bootstrap_inner
    self.run()
  File "C:\Python26\sign\OCTOBER29_mysqldevlopment.py", line 6884, in run
    cursor.execute("INSERT INTO newtable (Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp) VALUES ( %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s,%s, %s,%s, %s)",(Actual_Incident_number, Age,Gender,Zip,Ailment,Treatment,Initial_contact,Hospital,Destination,Inservice,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp));
  File "C:\Python26\lib\site-packages\MySQLdb\cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python26\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1054, "Unknown column 'Actual_Incident_number' in 'field list'")

Select allOpen in new window

Ok I found the problem in the table name ptrecords there is a space before "p" I guess that changes the whole meaning ...  but now Im getting

OperationalError: (1054, "Unknown column 'Actual_Incident_number' in 'field list'")

Select allOpen in new window

smh !

Thanks for everything ... this is my first go around with mysql and I was told it is very similar to sqlite... not true but I got my application working  thanks

You're welcome.

Well sqlite and mysql are similiar, but far from identical.
All the 'meta-commands' (get info about databases, tables, table structures) are different.
Mysql (and most other RDBM engines)  is nstrongly typed unlike SQLite,
. . .

If you want to write one application, which can easily be switched between multiple database engines, then you have to anticipate this upfront.

Some ways to do so might be:
- Test all queries with multiple data base engines. (often there is one sql syntax that works
    with all of them),
- add if statements, that depending on the db engine use one or the other query syntax.
- Use an abstraction library like SqlAlchemy / Django ORM (i never used SQLAlchemy though). In this case you will no more write SQL statements, but some abstraction library specific code, that will then be translated (on the fly) to the correct sql statements.

Abstraction libraries may have some limitations though.
They might be slightly less performant, and some SQL commands are difficult to create.
Getting started with an abstraction library might be a little complex and one has to learn a new syntax.


Have fun with your project.