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.
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.
>>> 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!)
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
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