0

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()
user229044
  • 232,980
  • 40
  • 330
  • 338
pepperjohn
  • 67
  • 1
  • 3
  • 11
  • https://stackoverflow.com/questions/25865270/how-to-install-python-mysqldb-module-using-pip see this to install mysql – Salmaan P Nov 26 '17 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 Nov 26 '17 at 23:20
  • did you import it using this import mysql.connector If it doesnt work, you can try the other ones. – Salmaan P Nov 26 '17 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 Nov 26 '17 at 23:31
  • https://dev.mysql.com/doc/connector-python/en/connector-python-installation.html – eagle Nov 26 '17 at 23:40
  • Hi Eagle, Thanks. I installed Mysqlconnector but when I ran the query I still see: No module named 'mysql'. I even changed it to import mysql.connector and database = mysql.connector.connect – pepperjohn Nov 26 '17 at 23:57

1 Answers1

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.

eagle
  • 872
  • 5
  • 14
  • 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? – pepperjohn Nov 27 '17 at 00:04
  • I just edited you might have been using what I posted prior to the edit – eagle Nov 27 '17 at 00:05
  • Ah thanks! Now I see the 'old' error message: No module named 'mysql' Any thoughts? – pepperjohn Nov 27 '17 at 00:07
  • yea it seems mysql isnt in your path – eagle Nov 27 '17 at 00:08
  • what does it mean not being in my path? Do I need to install another module? – pepperjohn Nov 27 '17 at 00:09
  • no it means that wherever you installed the `mysql` connector isn't in the same place where python is being read from. are you on a nix machine? – eagle Nov 27 '17 at 00:11
  • No, I am on a mac. but yes probably mysql connector it's not on the same place where python is being read from. Would you know how can I find the location from both to see the difference? I am not a technical person as you probably figured out already eheheh – pepperjohn Nov 27 '17 at 00:16
  • it says: /Users/joaopimenta/Documents/joaoPython/env/bin:/Users/joaopimenta/anaconda/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin – pepperjohn Nov 27 '17 at 00:29
  • I think mysql connector is not installed in that location but I don't know where it is. How would I find the location? – pepperjohn Nov 27 '17 at 00:34
  • it probably isn't did you follow the instructions on the link I gave you. but to find everywhere mysql is, do this from your disk's root directory, `find . -name mysql -type d` – eagle Nov 27 '17 at 00:38
  • If I write that code I don't see anything. Yes I believe I followed the instructions from the link: https://dev.mysql.com/downloads/connector/python/ On mac it seems that there's no need to write any code in terminal to install id, just download the dmg – pepperjohn Nov 27 '17 at 00:46
  • maybe I could get a more recent version. I got 2.1.7. If I go to the development releases I see version 8.0.5. Maybe? – pepperjohn Nov 27 '17 at 00:50
  • Hey mate, got it working. I had to write this command inside my environment: https://stackoverflow.com/questions/22100757/can-not-get-mysql-connector-python-to-install-in-virtualenv Found it in here: https://stackoverflow.com/questions/22100757/can-not-get-mysql-connector-python-to-install-in-virtualenv Thanks for your help and patience! – pepperjohn Nov 27 '17 at 10:48