Python connects to mysql database

I systematically learned how to connect java to mysql database before. Now I need to use the database when doing python, so I reorganized my database knowledge and became familiar with the functions and functions of the MysqlDB module in python. Now I will systematically learn about it. Let’s summarize:

First thing you have to do is to download the corresponding module and install it. Search the download address yourself. There are many on the Internet. Installation is also easy. After installation, click the site under the Lib folder in the python installation directory. The MySQLdb folder under the -packages folder contains the definition of the module. After the preparations are done, we need to import MySQLdb in the source code

Database connection

After the module is introduced, we need to connect to the database. The example code is as follows:

db = MySQLdb.connect("localhost"," root","123456","myciti" )

The meaning of these three key parameters is clear at a glance: the first is the address of the server, the second is the user name, the third is the dbms password, and the fourth is to access Database, in fact, the parameters of the connect function are more than these, but because they have default values ​​and do not need to be modified in most cases, they are omitted. Here is the following list:

host，连接的数据库服务器主机名，默认为本地主机(localhost)。
user，连接数据库的用户名，默认为当前用户。
passwd，连接密码，没有默认值。
db，连接的数据库名，没有默认值。
conv，将文字映射到Python类型的字典。默认为MySQLdb.converters.conversions
cursorclass，cursor()使用的种类，默认值为MySQLdb.cursors.Cursor。
compress，启用协议压缩功能。
named_pipe，在windows中，与一个命名管道相连接。
init_command，一旦连接建立，就为数据库服务器指定一条语句来运行。
read_default_file，使用指定的MySQL配置文件。
read_default_group，读取的默认组。
unix_socket，在unix中，连接使用的套接字，默认使用TCP。
port，指定数据库服务器的连接端口，默认是3306

You may notice that the port number is not used in the source code. This is because the default value of this parameter of MySQLdb's connect function is 3306. If you modified the database port when installing mysql number, then you need to add the modified value of this parameter in the source code.

Execute the sql statement

After connecting, the sql statement is executed. The source code is as follows:

import MySQLdb


db = MySQLdb.connect("localhost","root","123456","myciti " )
cursor = db.cursor()
sql = """insert into article values ​​(0,"woainimahah","http://www.aa.com","2012-9-8","wo" ,"qq","skjfasklfj","2019","up")"""
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close

What needs to be noted here is that you must remember to commit. If you do not submit, the database will not change.

Change the sql statement to something else, and you can achieve other static operations, such as "delete from article where id > 3”

Select and print

The most important purpose of connecting to the database is to read the information in the database, so how to obtain the data in the database? How to extract effective information? See the following code:

The following program can print the data in the second column of all rows in the article table in the database:

import MySQLdb


db = MySQLdb.connect("localhost","root","123456" ,"myciti" )
cursor = db.cursor()
cursor.execute("select * from article")
data = cursor.fetchone()
while data!=None:
print data[1]
data = cursor .fetchone()
db.close

We can also use the following code:

import MySQLdb


db = MySQLdb.connect("localhost","root","123456","myciti" )
cursor = db .cursor()
cursor.execute("select * from article")
datas = cursor.fetchall()
for data in datas:
print data[1]
print cursor.rowcount,"rows in tatal"
db. close

We can see the difference between the above functions from the code:

fetchone takes out a row of records from the database table, the second call takes out the next row, and keeps going down

fetchall takes out the database table The data of all rows in

rowcount reads out the number of rows in the database table

Like the placeholders in java, these placeholders are also needed in python to implement dynamic selection. See the following code:

import MySQLdb


a = "down"
b = 4
db = MySQLdb.connect("localhost","root","123456","myciti" )
cursor = db.cursor( )
cursor.execute("select * from article where trend = '%s' and id < '%d'"%(a,b))
datas = cursor.fetchall()
for data in datas:
print data[1]
print cursor.rowcount,"rows in tatal"
db.close

There is one difference between the placeholder here and the ? placeholder in java, that is, it declares the type, which is different from the placeholder in C The format output is the same. Pay attention to the following %

The returned data is also a list, and the data of a specific column can be accessed by using the [] operator.

Static insertion and dynamic insertion

We have already talked about static insertion above, so let’s talk about dynamic insertion next

Dynamic insertion is also implemented using placeholders

import MySQLdb


title = "wangxinmeiwo"
url = "henxiangni "
db = MySQLdb.connect("localhost","root","123456","myciti" )
cursor = db.cursor()
sql = """insert into article values ​​(0,"%s","%s","2012-9-8","wo","qq","skjfasklfj","2019","up")" ""
try:
cursor.execute(sql%(title,url))
db.commit()
except:
db.rollback()
db.close

You can see the placeholder here and the one above The usage is the same

update operation

The usage of placeholder is the same as above

import MySQLdb


title = "haoxiangni"
id=11
db = MySQLdb.connect("localhost ","root","123456","myciti" )
cursor = db.cursor()
sql = """update article set title = "%s" where id = "%d" """
try:
cursor.execute(sql%(title,id))
db.commit()
except:
db.rollback()
db.close

The above are the main operations involved in python accessing the database. Please indicate the source when reprinting