python 连接mysql

创建数据库

create database unicom default charset utf8 collate utf8_general_ci

use unicom

create table admin(
  id int not null auto_increment primary key,
  username varchar(16) not null,
  password varchar(64) not null,
  mobile char(11) not null
  ) default charset=utf8;

安装pymywql

pip install pymysql

插入数据

#创建连接
conn=pymysql.connect(host='127.0.0.1',user='root',password='123456',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#发送指令
cursor.execute("insert into admin(username,password,mobile) values ('lifei','123456','18611282478'),('lifei5','123456','18611282478')")
conn.commit()
#关闭连接
cursor.close()
conn.close()

插入数据拼接字符串

import pymysql
#创建连接
conn=pymysql.connect(host='127.0.0.1',user='root',password='123456',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#发送指令

# sql="insert into admin(username,password,mobile) values (%s,%s,%s)"
#
# cursor.execute(sql,['geiaiqing','lifei888','18211486789'])

sql="insert into admin(username,password,mobile) values (%(n1)s,%(n2)s,%(n3)s)"

cursor.execute(sql,{'n1':'zhaobo','n2':'aaaabbbb','n3':'18433432588'})

conn.commit()
#关闭连接
cursor.close()
conn.close()

查询数据

import pymysql
#创建连接
conn=pymysql.connect(host='127.0.0.1',user='root',password='123456',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#发送指令
#cursor.execute("select * from admin")
cursor.execute("select * from admin where id>%s",[2,])
# 获取符合条件的一条数据
#myresult=cursor.fetchone()
# 获取符合条件的所有数据
myresult=cursor.fetchall()
for x in myresult:
    print(x)
#关闭连接
cursor.close()
conn.close()