Access MySQL database with Python

The first thing to make clear is that we need third-party libraries in Python to access MySQL.

There are several ways: Mysql-python (i.e. mysqldb), Pymysql, Mysql-connector. Mysql-python is written in C, the fastest, and the latter is written in pure python, relatively slower. Unfortunately, Mysql-python only supports Python 2.7, and there is no support for Python 3.X. If you use Python3, you can't use Mysql-python. This article uses Python 3.4, a third-party library that is pymysql.

Installation of Pymysql

After the PIP is installed. Enter the command in cmd pip install Pymysql can be installed. For the installation and use of PIP please own Baidu.

After installing the Pymysql, the use of the same as the general third-party library, the need for prior import, the following I directly to an example, you can experiment from idle.

　　

>>>ImportPymysql>>> Conn=pymysql.connect (host='localhost', user='Root', passwd='Password', charset='UTF8', port=3306)
#port一般都是3306, CharSet to write UTF8, otherwise it may appear garbled>>> cur=Conn.cursor ()
#查看有哪些数据库>>> Cur.execute ('Show Databases')9>>> databases=[]>>> forIinchcur:databases.append (i)>>>databases[('Information_schema',), ('Firstdb',), ('Hive',), ('Jeesite',), ('MySQL',), ('School',), ('Test',), ('test1',), ('test2015',)]>>> conn.select_db ('Test')>>> Cur.execute ('Show Tables')7>>> tables_list=Cur.fetchall ()>>>Tables_list (('User',), ('User2',), ('User3',), ('User4',), ('User5',), ('User6',), ('User7',))>>> Cur.execute ('SELECT * from user')7>>> user_select_result=Cur.fetchall ()>>>User_select_result (('1','Michael'), (' One','Ozil'), (' A','Giroud'), ('2','Henry'), ('Alexis',' -'), ('Ramsey',' -'), ('Walcott',' -'))>>> Cur.execute ('SELECT * from user')7>>> user_select_result=Cur.fetchone ()>>>User_select_result ('1','Michael')>>> Cur.execute ('SELECT * from user')7>>> User_select_result=cur.fetchmany (4)>>>User_select_result (('1','Michael'), (' One','Ozil'), (' A','Giroud'), ('2','Henry'))>>> insert_value= ('3','Gibbs')>>> Cur.execute ('INSERT INTO User (Id,name) VALUES (%s,%s)', Insert_value)1>>> Cur.execute ('SELECT * from user')8>>> user_select_result=Cur.fetchall ()>>>User_select_result (('1','Michael'), (' One','Ozil'), (' A','Giroud'), ('2','Henry'), ('3','Gibbs'), ('Alexis',' -'), ('Ramsey',' -'), ('Walcott',' -')) Insert_value_list=[(' A','Debucy'),(' -','Cech')]>>> Cur.executemany ('INSERT INTO User (Id,name) VALUES (%s,%s)', Insert_value_list)2>>> Cur.execute ('SELECT * from user')10>>> user_select_result=Cur.fetchall ()>>>User_select_result (('1','Michael'), (' One','Ozil'), (' A','Giroud'), ('2','Henry'), (' A','Debucy'), ('3','Gibbs'), (' -','Cech'), ('Alexis',' -'), ('Ramsey',' -'), ('Walcott',' -'))>>>Conn.commit ()>>> Cur.execute ('Update user set name= "Ozil" where id= "one"')1>>> user_select_result=Cur.fetchall ()>>>User_select_result ()>>> Cur.execute ('SELECT * from user')10>>> user_select_result=Cur.fetchall ()>>>User_select_result (('1','Michael'), (' One','Ozil'), (' A','Giroud'), ('2','Henry'), (' A','Debucy'), ('3','Gibbs'), (' -','Cech'), ('Alexis',' -'), ('Ramsey',' -'), ('Walcott',' -'))
#修改后一定要comiit, the deleted, updated, and added data will not be written into the database. >>>Conn.commit ()>>>cur.close ()>>> Conn.close ()

Access MySQL database with Python