1

I have a Python string (or potentially a Python dictionary) that I'd like to insert to MySql table.
My String is the following: 

{'ticker': 'BTC', 'avail_supply': 16479075.0, 'prices': 2750.99, 'name': 'Bitcoin', '24hvol': 678995000.0}

this is the error I'm getting

I have the same kind of error if I want to insert the Dict format.
I really don't understand this kind of error (i.e. the '\' in-between the components of the string). How can I deal with this error? Any why to properly insert a whole string to a particular TEXT cell in SQL?
Many thanks !!

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
sammtt
  • 401
  • 1
  • 6
  • 14

1 Answers1

3

Here is how to connect, make a table, and insert in the table.

import MySQLdb as mdb
import sys
#connect
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con:
    #need the cursor object so you can pass sql commands, also there is a dictionary cursor
    cur = con.cursor()
    #create example table
    cur.execute("CREATE TABLE IF NOT EXISTS \
        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
    #insert what you want
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

Example above will make a table with 2 cols, one ID and one name

look here on an example on how to insert stuff from dictionary with keys and list as value to sql, basically you need place holders

sql = "INSERT INTO mytable (a,b,c) VALUES (%(qwe)s, %(asd)s, %(zxc)s);"
data = {'qwe':1, 'asd':2, 'zxc':None}

conn = MySQLdb.connect(**params)

cursor = conn.cursor()
cursor.execute(sql, data)
cursor.close()

conn.close()

or you can go with this as an example for a simple straight forward dict

placeholders = ', '.join(['%s'] * len(myDict))
columns = ', '.join(myDict.keys())
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
cursor.execute(sql, myDict.values())
Fabhi
  • 17
  • 6
BlooB
  • 955
  • 10
  • 23
  • Many thanks for your answer. However I'm not sure it would input the whole string into one specific cell, or is it? – sammtt Aug 02 '17 at 09:17
  • Any news? @dirty_feri – sammtt Aug 02 '17 at 20:31
  • look at the last example, the placeholder looks at how many key, value pairs you have and gives you a way to have a place holder, your keys will be columns. Than you make the statement that the database can understand. In the Insert statement first %s is the table, second one is the column that you made in line before and last one is the place holder. last line is executing your sql string and actually putting the values in the database. Sorry that i am traveling and dont have access to pc to demonstrate this for you. – BlooB Aug 03 '17 at 02:18
  • @sammtt which python are you using? if you are using python3 change the last line in example to cursor.execute(sql, list(dict.values())) – BlooB Aug 03 '17 at 02:21
  • thanks for the answer @dirty_feri. However, I don't think this is doing what I want to do. I want to insert the full json string as it is to one specific cell of the table; e.g. row1 of column1 has the content: {'ticker': 'BTC', 'avail_supply': 16479075.0, 'prices': 2750.99, 'name': 'Bitcoin', '24hvol': 678995000.0} – sammtt Aug 03 '17 at 10:28
  • So your table is 1×1? – BlooB Aug 03 '17 at 18:16
  • it is not but I want to insert the whole JSON string into 1x1 cell yes – sammtt Aug 04 '17 at 09:20
  • why dont you make a for loop and than extract what you want from dictionary and make a string out of all of them, in the for loop simply use Insert. In the insert statement do something like, INSERT INTO YOUR_TABLE_NAME(COLUMN_NAME) VALUES(YOUR_STRING). that way you can also control what goes where. Than outside of the for loop do commit() – BlooB Aug 04 '17 at 21:47