CODEWITHSUNDEEP
pythonmysqlstringdictionary
sammtt
sammtt

Reputation: 421

Insert Python string or dictionary into MySQL

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 !!

Upvotes: 1

Views: 7023

Answers (1)

BlooB
BlooB

Reputation: 965

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())

Upvotes: 3

Related Questions