2

My current script saves to an excel file but i would like to directly upload it to my mysql database.

  for i in range(0, len(names)):
            ws.append([names[i], str((float(sells[i])+float(buys[i]))/2.0)])  
    except:
        print('Cannot fetch from this link')  
print('Saving the file with name markets.xlsx')
wb.save("markets.xlsx")

how would i perform a loop to do the following 

 sql = "INSERT INTO todaysmarkets(Name,value) VALUES(%s,%s)"

****Update*****

following the advice on here i now have the following:

cnx = mysql.connector.connect(host='.com', user='', password='', database='')
        cursor = cnx.cursor()
        for i in range(0, len(names)):
           cursor.execute(("INSERT INTO todaysmarkets(Name,value) VALUES(%s,%s)"), (names[i], str((float(sells[i])+float(buys[i]))/2.0)))
           cnx.close()

script runs through with no errors but the database is not updated.

Improve this question
3
  • So you don't use ORM of any sort? Did you see mysql documentation on how to connect to it from Python? dev.mysql.com/doc/connector-python/en/… Commented Apr 11, 2016 at 10:28
  • Can you maybe update your question with your current sample and error stack? Commented Apr 11, 2016 at 11:28
  • Ok, can you confirm you can connect to your database with console command mysql without user/password/database selected? I would guess you need these setup first (use mysql console command, connect to mysql, create database, create user with password and grant user rights to connect to 127.0.0.1 or to localhost). You will also need your table todaymarkets created with columns Name and value. You will then need to update your connection string (`mysql.connector.connect(host='127.0.0.1', user='your_user', password='your_password', database='your_database') Commented Apr 11, 2016 at 20:19

3 Answers 3

Reset to default
1

Before I go and point you to the resources I'll mention something you probably already know - building SQL queries like the one from your exampe leads to high potential somebody may provide unwanted strings and as a result execute some sort of sql injection. Of course that may not be true if you are building a tool you are the only one who will be using. I'm not frequent mysql user and I don't interact with sql directly so I also am not sure if mysql would do all of the escaping for you to prevent potential SQL injection. In PHP world mysqli would do that (as far as I'm aware).

MySQL gives something for a good start when developing under Python.

https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

I found it is not straight forward to install mysql connector - found help here: I cannot install mysql-connector-python using pip

Example Python code (combined by simply copying/pasting from provided resource) would look like this:

import mysql.connector
cnx = mysql.connector.connect(user='scott', password='tiger',
                          host='127.0.0.1',
                          database='employees')
cursor = cnx.cursor()
for i in range(0, len(names)):
    cursor.execute(("INSERT INTO todaysmarkets(Name,value) VALUES(%s,%s)"), (names[i], str((float(sells[i])+float(buys[i]))/2.0)))

cnx.close()

Of course this example "assumes" you will create your employees database and grant scott access to this database from 127.0.0.1 with password tiger before you run your script.

If you decided to go for ORM (i.e. SQLAlchemy) your queries would look much easier to read and also with other tools like Alembic you could automate migration of your schema if you needed one in the future (again this would be an overkill if you was developing for your own tool)

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

3 Comments

I edited the script and added it to my existing one but get the error "AttributeError: Unsupported argument 'cursorclass'" (also this is just my own tool)
What if you try to change your INSERT INTO todaysmarkets(Name,value) VALUES(%s,%s) to this: INSERT INTO todaysmarkets(Name,value) VALUES(%(name)s,%(value)s) and data like this: {'name':names[i], 'value':str((float(sells[i])+float(buys[i]))/2.0))}. Also can you post your edited sample?
I have done the following: cnx = mysql.connector.connect(host='.com', user='', password='', database='') cursor = cnx.cursor() for i in range(0, len(names)): cursor.execute(("INSERT INTO todaysmarkets(Name,value) VALUES(%s,%s)"), (names[i], str((float(sells[i])+float(buys[i]))/2.0))) cnx.close() but it does not add anything into the database
1

First, you should have some library to do the heavy lifting for you. You should try the official mysql.connector.

Using it is simple.

connection = mysql.connector.connect(
                    host=MYSQL_SERVER_ADDRESS,
                    port=MYSQL_SERVER_PORT,
                    user=MYSQL_SERVER_USER,
                    password=MYSQL_SERVER_PASS,
                    database=MYSQL_SERVER_DB
)

cursor = connection.cursor()
cursor.execute(SQL_STATEMENT)

or if you want to use it with multiple params you should use the executemany

cursor.executemany(operation, seq_of_params)

in your case that would be:

cursor.executemany(sql, ws)  # sql is the string you posted, and ws is the data
Improve this answer

Comments

1

You need to implement the following changes:

  1. Don't call cnx.close() in the loop
  2. According to Inserting into MySQL table in Python you need to call cnx.commit() to persist the changes:

Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.