Python save to mysql database

Question

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.

So you don't use ORM of any sort? Did you see mysql documentation on how to connect to it from Python? https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html — Greg0ry, Apr 11 '16 at 10:28
Can you maybe update your question with your current sample and error stack? — Greg0ry, Apr 11 '16 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') — Greg0ry, Apr 11 '16 at 20:19

score 1 · Accepted Answer · edited May 23 '17 at 11:50

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)

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) — emma perkins, Apr 11 '16 at 10:51
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? — Greg0ry, Apr 11 '16 at 10:58
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 — emma perkins, Apr 11 '16 at 13:02

score 1 · Answer 2 · answered Apr 11 '16 at 10:32

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

score 1 · Answer 3 · edited Feb 17 '21 at 17:20

1

You need to implement the following changes:

Don't call cnx.close() in the loop
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.

edited Feb 17 '21 at 17:20

pfabri

885
1
9
25

answered Feb 17 '21 at 15:12

Chai Ross

11
2

Python save to mysql database

3 Answers3