How to store data in MySQL database via python

Question

This is the script that I wrote so far. The first blocker that I find is that I am not being able to install MySQLdb package - Maybe I could use a different module?

import soundcloud
import pandas as pd 
from pandas import DataFrame
import MySQLdb
client = 
soundcloud.Client(client_id='696b5ca70f5401cc46c9011c78831877')
userId = '110652450'
tracks = client.get('/users/'+userId+'/tracks')
data = []
for x in tracks:
    data.append({'Track_Name':x.title,'plays':str(x.playback_count)}) 

df = pd.DataFrame(data)

database = MySQLdb.connect (host="127.0.0.1",user ="root",passwd="XXX",db="soundcloudstore")
cursor = database.cursor()
query = """INSERT INTO Tracks (Track_Name, Plays) VALUES (%s,%s)"""

for x in df:
    Track_Name = df[['Track_Name']].value 
    Plays = df[['plays']].value 

    values = (Track_Name, Plays)
    cursor.execute(query, values)

cursor.close() 

database.commit()

database.close()

stackoverflow.com/questions/25865270/… see this to install mysql — Salmaan P
– Salmaan P, Commented Nov 26, 2017 at 23:12
Thanks Salmaan, but if I install brew install mysql-connector-c (which works) still doesn't allow me to use the module 'MySQLdb'. Would you recommend a different module perhaps? — pepperjohn
– pepperjohn, Commented Nov 26, 2017 at 23:20
did you import it using this import mysql.connector If it doesnt work, you can try the other ones. — Salmaan P
– Salmaan P, Commented Nov 26, 2017 at 23:23
I am not sure if I understood what you said but I also tried: pip install mysql.connector and I got the message 'Could not find a version that satisfies the requirement mysql.connector (from versions: ) No matching distribution found for mysql.connector' — pepperjohn
– pepperjohn, Commented Nov 26, 2017 at 23:31

eagle · Accepted Answer · 2017-11-26 23:58:54Z

0

Download the adapter here: https://dev.mysql.com/doc/connector-python/en/connector-python-installation.html

Then you would use it as so:

import mysql.connector

data = []
for x in tracks:
    data.append((x.title, str(x.playback_count))) 

conn = mysql.connector.connect(user='', password='',
                              host='',
                              database='')

cursor = conn.cursor()

q = """INSERT INTO Tracks (Track_Name, Plays) VALUES (%s,%s)"""

cursor.executemany(q, data)

This will save you from loading into a dataframe for no reason and executemany is optimized for inserts.

answered Nov 26, 2017 at 23:58

eagle

9006 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

pepperjohn Over a year ago

Hi Eagle, Thanks. If I ran the script above I see the error 'invalid syntax' which I think is in the append section. Do you know how could I fix it?

eagle Over a year ago

I just edited you might have been using what I posted prior to the edit

pepperjohn Over a year ago

Ah thanks! Now I see the 'old' error message: No module named 'mysql' Any thoughts?

eagle Over a year ago

yea it seems mysql isnt in your path

pepperjohn Over a year ago

what does it mean not being in my path? Do I need to install another module?

|

Collectives™ on Stack Overflow

How to store data in MySQL database via python

1 Answer 1

13 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Linked

Related