• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3203
  • Last Modified:

TypeError: not all arguments converted during string formatting

what is the python syntax for mysql

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 6885, in run
    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 all arguments converted during string formatting

Open in new window



      
  #get transferred information
	    conn = lite.connect("PermPatRecord.sqlite")
	    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]))
			
		conn = MySQLdb.connect (host = 'localhost', user = 'root' ,passwd = '', db = 'transferdb')
		
		#connect = lite.connect('backup.sqlite')
		cur = conn.cursor()
		cur.execute('INSERT INTO ptrecords VALUES(%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));

Open in new window

0
dolamitejenkins
Asked:
dolamitejenkins
  • 8
  • 6
1 Solution
 
gelonidaCommented:
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));

Open in new window

0
 
dolamitejenkinsAuthor Commented:
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()

Open 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

Open in new window



   
0
 
dolamitejenkinsAuthor Commented:
I found the error here ... now im getting a whole new error ... will keep you updated
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
gelonidaCommented:
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))

Open in new window

0
 
dolamitejenkinsAuthor Commented:
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")

Open in new window

0
 
gelonidaCommented:
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.
0
 
dolamitejenkinsAuthor Commented:
sorry for my newnes but are you talking about the MYSQL5.5 Command Line Client ?
0
 
gelonidaCommented:
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
0
 
gelonidaCommented:
You can also use attached script instead
import getpass
import MySQLdb

db = 'transferdb'
table = 'ptrecords'
passwd = getpass.getpass('please enter the password: ')
#passwd = '' # or hardcoded value here

conn = MySQLdb.connect(host='localhost', user='root', 
    passwd=passwd, db=db)

cur = conn.cursor()

cur.execute('show databases')
print "my databases"
for row in cur:
    print row

cur.execute('show tables')
print "\nmy tables"
for row in cur:
    print row

cur.execute('describe %s' % table)
print "\n info about table %s" % table
for row in cur:
    print row

Open in new window

0
 
dolamitejenkinsAuthor Commented:
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

Open in new window

0
 
dolamitejenkinsAuthor Commented:
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, '')

Open 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'")

Open in new window



0
 
dolamitejenkinsAuthor Commented:
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'")

Open in new window

smh !
0
 
dolamitejenkinsAuthor Commented:
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
0
 
gelonidaCommented:
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now