Python operation MySQL database needs to install the Pymysql module, in the previous blog can be turned to see how to install, the simplest is the PIP install Pymysql
1. Importing Pymysql module Import pymysql
2. Connect the database, need IP, account, password, port number, database and other information
3. Creating Cursors
4. Execute SQL statements
5. Get Results
6. Close the cursor
7. Close the connection
Import Pymysqlcoon= Pymysql.connect (host='192.168.21.129', user='Root', passwd='123456', port=3306, db='Test', charset='UTF8') cur=coon.cursor () SQL='SELECT * from user'cur.execute (SQL) Res=Cur.fetchall () print (res) cur.close () coon.close ()
Import Pymysqlcoon= Pymysql.connect (host='192.168.21.129', user='Root', passwd='123456', port=3306, db='Test', charset='UTF8') cur=coon.cursor () #建立游标cur. Execute ('INSERT INTO User (USERNAME,PASSWD) VALUE ("2222", "4444");'#执行sqlcoon. Commit () #delete update insert must have Coomitcur.close () #关闭游标coon. Close () #关闭连接
Instead of executing the SELECT statement and the DELETE update INSERT statement, we can define a function that uses conditions in the function to determine the SQL statement
def my_db (host,user,passwd,db,sql,port=3306, charset='UTF8'): Import Pymysql Coon= Pymysql.connect (user=User, host=Host, Port=Port, passwd=passwd, DB=db, CharSet=charset) Cur=coon.cursor () #建立游标 cur.execute (SQL) #执行sqlifSql.strip () [:6].upper () = ='SELECT': #sql语句切片取前6位, then convert to uppercase, then compare Res=Cur.fetchall ()Else: Coon.commit () Res='OK'cur.close () coon.close ( )returnRes
Python (vii) operation MySQL