python串連MySQL

標籤：new   eth   pytho   元素   work   close   isp   alt   pen   

    再python3中串連mysql的模組位pymysql，我們可以通過pip來安裝：

1 pip install pymysql

這樣我們就可以使用該模組串連mysql了。

串連mysql用到pymysql的Connect()模組，

 1 Connect(*args, **kwargs)  #Connect方法 2     Establish a connection to the MySQL database. Accepts several 3     arguments: 4  5     host: Host where the database server is located 6     user: Username to log in as 7     password: Password to use. 8     database: Database to use, None to not use a particular one. 9     port: MySQL port to use, default is usually OK. (default: 3306)10     bind_address: When the client has multiple network interfaces, specify the interface from which to connect to the host. Argument can be a hostname or an IP address.11     unix_socket: Optionally, you can use a unix socket rather than TCP/IP.12     charset: Charset you want to use.

串連資料庫後需要設定遊標（cursor）進行資料庫的執行操作conn.cursor():

1 >>> help(conn.cursor) #建立遊標2 Help on method cursor in module pymysql.connections:3 4 cursor(cursor=None) method of pymysql.connections.Connection instance5     Create a new cursor to execute queries with

設定了cursor後，就可以寫sql語句，使用cursor的執行個體去執行了,執行用到execute()方法：

 1 >>> help(cursor.execute)   #execute方法 2 Help on method execute in module pymysql.cursors: 3  4 execute(query, args=None) method of pymysql.cursors.Cursor instance 5     Execute a query 6  7     :param str query: Query to execute. 8  9     :param args: parameters used with query. (optional)10     :type args: tuple, list or dict11 12     :return: Number of affected rows13     :rtype: int14 15     If args is a list or tuple, %s can be used as a placeholder in the query.16     If args is a dict, %(name)s can be used as a placeholder in the query.

執行sql語句後，查詢返回的結果，可以使用cursor的fetchall()函數查看，這會返回一個元組，每個元組的元素還是一個元組cursor.fetchall()：

1 >>> help(cursor.fetchall)2 Help on method fetchall in module pymysql.cursors:3 4 fetchall() method of pymysql.cursors.Cursor instance5     Fetch all the rows

可以使用for迴圈逐個查看結果。

下面是一個完整的例子：

 1 import pymysql 2  3 conn=pymysql.Connect(host=‘192.168.37.130‘,port=3306,user=‘root‘,password=‘123456‘,database=‘user‘) 4 cursor=conn.cursor(pymysql.cursors.DictCursor) 5 sql=‘select * from users‘ 6 res_num=cursor.execute(sql) 7 print(res_num) 8 res_content=cursor.fetchall() 9 for i in res_content:10     print(i)

View Code 

上面類比了一個簡單的資料庫查詢操作。下面我們在寫一個簡單的例子：

 1 import pymysql 2  3 conn=pymysql.Connect(host=‘192.168.37.130‘,port=3306,user=‘root‘,password=‘123456‘,database=‘user‘) 4 cursor=conn.cursor(pymysql.cursors.DictCursor) 5 name=input(‘Input name:‘).strip() 6 sql=‘select * from users where name like %s‘ 7 res_num=cursor.execute(sql,name ) 8 if res_num>0: 9     res_content=cursor.fetchall()10     for i in res_content:11         print(i)12 else:13     print(‘Not in database‘)

View Code

 

python串連MySQL