Let's do MySQL data manipulation with Python

I usually use Django to immerse myself in the benefits of O / R mappers, so if I'm not good at it Data manipulation that can be done without even writing SQL statements in a table that has nothing to do with Django Can you say "Sir, yes sir!" When asked to try it? I could not! Then I tried my best so I will summarize it! Qiita article starts!

To be honest, what you do does not change much in any language, so if you remember one, you can apply it in other languages as well. I think it will work.

MySQL DB connector

If you're using Django, you've got a MySQL connector, right? Alright, next! (I like which one is better, so I omit it) For the time being, this article is for MySQL-python 1.2.5.

Let's connect

>>> import MySQLdb

>>>> conn = MySQLdb.connect(host='hoge_host', user='hoge_user', passwd='hogehoge', db='hoge1')

Let's make a connection for the time being! MySQLdb has connection () and connect (), but it uses connect (). the reason is,

>>> conn = MySQLdb.connection(host='hoge_host', user='hoge_user', passwd='hogehoge', db='hoge1')
>>> print type(conn)
<type '_mysql.connection'>
>>> conn = MySQLdb.connect(host='hoge_host', user='hoge_user', passwd='hogehoge', db='hoge1')
>>> print type(conn)
<class 'MySQLdb.connections.Connection'>

Yes, do you understand? If you connect with connection (), _mysql.connection will be A MySQLdb.connections.Connection object was created when connecting with connect ().

The _mysql class is wrapped in the base class of MySQLdb and the function is added to MySQLdb. Since the cursor is implemented in MySQLdb, connect () is an option to use for connection. Be careful because it is a addictive point around here! (Although I use connect (), there are many trap samples where the variable name is connection!)

Anyway, it's SELECT, call it SELECT!

If you can connect, let's try the SELECT statement as soon as possible. After that, I will write on the assumption that there is a connected conn object.

cursor = conn.cursor()
try:
  cursor.execute('select * from hoge_table where id=1')
  result = cursor.fetchall()
finally:
  cursor.close()
  conn.close()

What is a cursor? A concept that cannot be said in a single word. Really a concept. Some people explain that it is a virtual area for sequentially performing multiple operations on the DB. Think of it like that. If you want to know it properly, go to the sea on the net! You can issue SQL with conn.query ('select ~') without using it, Since it is not possible to execute transactions for multiple operations, it is common to use cursors for DB operations.

Furthermore, the SQL statement cannot be retrieved as an object just by issuing it, but as a result set. You need to get it. That will be cursor.fetchall (). There are also fetchone () and fetchmany (), but I think you can use them properly as needed.

The retrieved data is, for example, a table hoge_table

id name hoge
1 hogee It's hoge
2 hogege It's hoge?

so,

cursor.execute('select * from hoge_table')
result = cursor.fetchall()

If you take (roughly omitted), the content of result is

((1, 'hogee', 'It's hoge'), (2, 'hogege', 'It's hoge?'))

It will be. e? What is the column name? You might have thought, ** It's sweet ** Thank you to O / R Mapper and prepare your own column name list.

Finally, the finally clause releases the cursor and connection, ** Bukkake with clause ** However, there is no loss in knowing it as a method. In other languages you have to write it yourself or not. ~~ Well, it doesn't break so much even if I don't release it! ~~ It is the programmer's duty to release valuable resources promptly

All right, then UPDATE!

Let's update next. Wouldn't it be convenient if we could do batch updates in batch processing of Python scripts?

conn = MySQLdb.connect(host='hoge_host', user='hoge_user', passwd='hogehoge', db='hoge1')
conn.autocommit(False)

cursor = conn.cursor()
try:
  cursor.execute('update hoge_table set hoge="It's hoge!" where id=1')
  cursor.execute('select * from hoge_table')
  result = cursor.fetchall()
  conn.commit()
except Exception as e:
  conn.rollback()
  raise e
finally:
  cursor.close()
  conn.close()

In the second line, autocommit is set to False, but it is certainly the default False, so it is unnecessary to say that it is unnecessary. Well, it may depend on the connector, so it is safer to explicitly set it to False. If you set it to True, it will commit without permission (that's right). If False, call commit () yourself. We also call rollback () ourselves. It is safer to spend this amount of time and effort if you can do it behind the scenes.

The SELECT statement is issued on the line following the UPDATE statement, but this may be the validation of the update result. You can do it with this! This is an example. If you don't like the result, make an exception and have it rolled back. Since this area cannot be created without using the cursor, connect () is still an option. This is reflected in the DB by calling the commit () function.


That's all for this time. With this, you can suddenly operate DB without O / R mapper. Even if a task comes in, we will be able to handle it. Also, the fact that the framework usually acts on your behalf is also a matter of content. I think that knowing it will help further understanding.

"Wait a minute! There is no INSERT or DELETE"

What to do is not much different from UPDATE, so it is omitted

"Wait a minute! There is no ALTER"

Need to do it with a script?

Recommended Posts

Let's do MySQL data manipulation with Python
Receive textual data from mysql with python
Data analysis with python 2
Do Houdini with Python3! !! !!
Data manipulation with Pandas!
Data analysis with Python
Let's do web scraping with Python (weather forecast)
Let's do web scraping with Python (stock price)
Do Django with CodeStar (Python3.6.8, Django2.2.9)
Let's run Excel with Python
Let's write python with cinema4d.
Let's do R-CNN with Sklearn-theano
Regular expression manipulation with Python
Let's build git-cat with Python
Connection pooling with Python + MySQL
Read json data with python
Notes on importing data from MySQL or CSV with Python
Basic summary of data manipulation with Python Pandas-First half: Data creation & manipulation
Let's make a GUI with python.
Build Mysql + Python environment with docker
[Python] Get economic data with DataReader
Let's play with Excel with Python [Beginner]
To do tail recursion with Python2
Python data structures learned with chemoinformatics
What to do with PYTHON release?
Easy data visualization with Python seaborn.
Let's make a graph with python! !!
Process Pubmed .xml data with python
Data analysis starting with python (data visualization 1)
Data analysis starting with python (data visualization 2)
Let's analyze voice with Python # 1 FFT
Python application: Data cleansing # 2: Data cleansing with DataFrame
Python | What you can do with Python
Get data from MySQL on a VPS with Python 3 and SQLAlchemy
Get additional data in LDAP with python
Data pipeline construction with Python and Luigi
INSERT into MySQL with Python [For beginners]
[Note] Get data from PostgreSQL with Python
Let's make a shiritori game with Python
Process Pubmed .xml data with python [Part 2]
Add a Python data source with Redash
Retrieving food data with Amazon API (Python)
Try working with binary data in Python
Generate Japanese test data with Python faker
Let's create a free group with Python
Convert Excel data to JSON with python
Let Heroku do background processing with Python
[Python] Use string data with scikit-learn SVM
Download Japanese stock price data with python
[Introduction to Python] Let's use foreach with Python
Let's read the RINEX file with Python ①
Manipulate DynamoDB data with Lambda (Node & Python)
Let's make a voice slowly with Python
Convert FX 1-minute data to 5-minute data with Python
How to do portmanteau test with python
[Python] Chapter 04-02 Various data structures (list manipulation)
[Python] Let's make matplotlib compatible with Japanese
Debug for mysql connection with python mysql.connector
[Python] Chapter 04-07 Various data structures (dictionary manipulation)
Data analysis starting with python (data preprocessing-machine learning)
Organize data divided by folder with Python