title: Python MySQLdb TypeError with formatting problems
body: <p>I'm new to Python,and recently I'm trying to use MySQLdb module to access to my MySQL server,the code goes like this.</p>
  
  <pre><code>import MySQLdb
  
  def convert(value):
      if value.startswith('~'):
          return value.strip('~')
      if not value:
          value='0'
      return float(value)
  
  conn=MySQLdb.connect(host='localhost',user='root',passwd='Timeless418',
          port=3306,db='pythonsql')
  curs=conn.cursor()
  
  try:
      curs.execute('drop table food')
  except Exception:   
      pass
  
  curs.execute('''
          create table food(
          id          char(40)    primary key,
          description char(200),
          water       float,
          kcal        float,
          protein     float,
          fat         float,
          ash         float,
          carbs       float,
          fiber       float,
          sugar       float
          );
          ''')
  
  query="insert into food values (?,?,?,?,?,?,?,?,?,?);"#10 value to formatting
  for line in open('ABBREV.txt'):
      fields=line.split('^')
      vals=[convert(f) for f in fields[:10]]
      print vals
      curs.execute(query,vals)
  conn.commit()
  conn.close()
  </code></pre>
  
  <p>The connection is successful,but when the program trys to run this:</p>
  
  <pre><code>curs.execute(query,vals)
  </code></pre>
  
  <p>The system gives this:</p>
  
  <pre><code>['06075', 'SOUP,BF BROTH OR BOUILLON,PDR,DRY', 3.27, 213.0, 15.97, 8.89, 54.5, 17.4, 0.0, 16.71]
  Traceback (most recent call last):
    File "D:\MyCodes\Python\13.2.2 import data into db.py", line 39, in &lt;module&gt;
      curs.execute(query,vals)
    File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 184, in execute
      query = query % db.literal(args)
  TypeError: not all arguments converted during string formatting
  </code></pre>
  
  <p>Could you tell me why this happens and how to fix it?thanks.</p>
  
  <p>FYI:</p>
  
  <p>some of the original data(ABBREV.txt):</p>
  
  <pre><code>~06075~^~SOUP,BF BROTH OR BOUILLON,PDR,DRY~^3.27^213^15.97^8.89^54.50^17.40^0.0^16.71^60^1.00^51^320^446^26000^0.00^0.000^0.459^27.6^0.0^0.070^0.243^4.467^0.300^0.200^32^0^32^32^113.4^1.00^0^0^0^0^0^0^0^0^2.17^0.0^0^3.2^4.320^3.616^0.332^10^3.6^~1 cube~^6.0^~1 packet~^0
  ~06076~^~SOUP,BEEF BROTH,CUBED,DRY~^3.30^170^17.30^4.00^59.30^16.10^0.0^14.51^60^2.23^50^225^403^24000^0.21^0.000^0.386^27.6^0.0^0.200^0.240^3.300^0.300^0.200^32^0^32^32^113.4^1.00^1^0^0^0^0^0^0^0^0.00^0.0^0^0.0^1.990^1.670^0.160^4^3.6^~1 cube~^^~~^0
  </code></pre>
  
  <p><strong>Thanks to everybody's help,I have solved this problem</strong></p>
  
  <p>For the query,I changed it to :</p>
  
  <pre><code>    query="insert into food values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
  </code></pre>
  
  <p>Every <code>%s</code> indicates a placeholder</p>
  
  <p>Since one of vals is this:</p>
  
  <p><code>['06075', 'SOUP,BF BROTH OR BOUILLON,PDR,DRY', 3.27, 213.0, 15.97, 8.89, 54.5, 17.4, 0.0, 16.71]</code></p>
  
  <p>And then execute:</p>
  
  <p><code>curs.execute(query,vals)</code></p>
  
  <p>It worked! I suppose there is a way that the <code>execute</code> will adjust my <code>vals</code>,and fill the <code>query</code>,for <code>str</code> object,it will add <code>''</code> to value,and for int or float,it will just put the value in the <code>query</code></p>
  
  <p><strong>Again,Thanks to everybody.</strong></p>