1 >>> import pymysql 2 >>> conn=pymysql.connect(host='localhost',user='root',passwd='password',charset='utf8',port=3306) 3 #port一般都是3306，charset要写utf8，不然可能会出现乱码 4 >>> cur=conn.cursor() 5 #查看有哪些数据库 6 >>> cur.execute('show databases') 7 >>> databases=[] 8 >>> for i in cur: 9 databases.append(i) 10 >>> databases 11 [('information_schema',), ('firstdb',), ('hive',), ('jeesite',), ('mysql',), ('school',), ('test',), ('test1',), ('test2015',)] 12 #选择数据库 13 >>> conn.select_db('test') 14 #如果一开始就知道选什么数据库，可以把数据库参数加到connect的语句里： 15 #conn=pymysql.connect(host='localhost',user='root',passwd='password',db='test',charset='utf8',port=3306) 16 #查看有哪些表 17 >>> cur.execute('show tables') 18 #fetchall是获得所有的查询结果 19 >>> tables_list=cur.fetchall() 20 >>> tables_list 21 (('user',), ('user2',), ('user3',), ('user4',), ('user5',), ('user6',), ('user7',)) 22 #创建table 23 >>> cur.execute('create table user8(id varchar(10),name varchar(10))') 24 #如果习惯于每一个colmn单独一行，可以用'''代替' 25 >>> cur.execute('''create table user8(id varchar(10), 26 name varchar(10))''') 27 #查看表user,execute中的语句语法跟mysql中的一样 28 >>> cur.execute('select * from user') 29 >>> user_select_result=cur.fetchall() 30 >>> user_select_result 31 (('1', 'Michael'), ('11', 'ozil'), ('12', 'Giroud'), ('2', 'Henry'), ('Alexis', '17'), ('Ramsey', '16'), ('Walcott', '14')) 32 >>> cur.execute('select * from user') 33 #fetchone只获得第一条查询结果 34 >>> user_select_result=cur.fetchone() 35 >>> user_select_result 36 ('1', 'Michael') 37 >>> cur.execute('select * from user') 38 #fetchmany(n)，可以获得n条查询结果 39 >>> user_select_result=cur.fetchmany(4) 40 >>> user_select_result 41 (('1', 'Michael'), ('11', 'ozil'), ('12', 'Giroud'), ('2', 'Henry')) 42 #插入数据，注意插入语句的插入参数一定要是变量，不能是直接一个set 43 >>> insert_value=('3','gibbs') 44 >>> cur.execute('insert into user(id,name) values(%s,%s)',insert_value) 45 >>> cur.execute('select * from user') 46 >>> user_select_result=cur.fetchall() 47 >>> user_select_result 48 (('1', 'Michael'), ('11', 'ozil'), ('12', 'Giroud'), ('2', 'Henry'), ('3', 'gibbs'), ('Alexis', '17'), ('Ramsey', '16'), ('Walcott', '14')) 49 insert_value_list=[('22','debucy'),('33','cech')] 50 #插入多条数据，需要用executemany 51 >>> cur.executemany('insert into user(id,name) values(%s,%s)',insert_value_list) 52 >>> cur.execute('select * from user') 53 >>> user_select_result=cur.fetchall() 54 >>> user_select_result 55 (('1', 'Michael'), ('11', 'ozil'), ('12', 'Giroud'), ('2', 'Henry'), ('22', 'debucy'), ('3', 'gibbs'), ('33', 'cech'), ('Alexis', '17'), ('Ramsey', '16'), ('Walcott', '14')) 56 #只有conn.commit()后，对数据库的修改才会提交 57 >>> conn.commit() 58 >>> cur.execute('update user set name="Ozil" where id="11"') 59 >>> user_select_result=cur.fetchall() 60 >>> user_select_result 61 () 62 >>> cur.execute('select * from user') 63 >>> user_select_result=cur.fetchall() 64 >>> user_select_result 65 (('1', 'Michael'), ('11', 'Ozil'), ('12', 'Giroud'), ('2', 'Henry'), ('22', 'debucy'), ('3', 'gibbs'), ('33', 'cech'), ('Alexis', '17'), ('Ramsey', '16'), ('Walcott', '14')) 66 #修改后一定要comiit，不然删除、更新、添加的数据都不会被写进数据库中。 67 >>> conn.commit() 68 #最后要把cur和conn都关掉 69 >>> cur.close() 70 >>> conn.close()