To connect to a database:
Import PYMYSQL,XLWT
Coon=pymysql.connect (host= ' localhost ', port=3306,user= ' jxz ', password= ' 123456 ', db= ' jxz ', charset= ' UTF8 ') #连接数据库
Cur=coon.cursor () #指定一个游标, sets the entry for the database, the return value is the tuple format by default
#cur =coon.cursor (cursor=pymysql.cursors.dictcursor) #指定一个游标且返回值是字典的格式
Database related operations:
Cur.execute (' select * from Nhy; ') #执行sql语句
Data=cur.fetchall () #获取数据库的数据/fetchall () reading is worthwhile, the next read can only continue reading from the last unread point
# Print (Cur.fetchone ()) #只获取一条/fetchone () reading is worthwhile, the next read can only continue reading from the last unread point
# Print (Cur.fetchmany (2)) #指定获取几条/fetchmany () reading is worthwhile, the next read can only continue reading from the last unread point
#cur. Execute (' INSERT into nhy values ("110", "2019", "3360"); #执行sql语句
Cur.execute (' select * from Nhy; ')
Coon.commit () #增删改数据的时候需要提交, if added autocommit=true eg:coon=pymysql.connect (host= ' 118.24.3.40 ', port=3306,user= ' jxz ',
# password= ' 123456 ', db= ' jxz ', charset= ' UTF8 ', autocommit=true) then commit () can not write
Cur.close () #关闭游标
Coon.close () #关闭数据库
Manipulating Database Python