>>> import pymysql

>>> conn=pymysql.connect(host='localhost',user='root',passwd='password',charset='utf8',port=3306)

#port一般都是3306，charset要写utf8，不然可能会出现乱码

>>> cur=conn.cursor()

#查看有哪些数据库

>>> cur.execute('show databases')

>>> databases=[]

>>> for i in cur:

    databases.append(i)

>>> databases

[('information_schema',), ('firstdb',), ('hive',), ('jeesite',), ('mysql',), ('school',), ('test',), ('test1',), ('test2015',)]

#选择数据库

>>> conn.select_db('test')

#如果一开始就知道选什么数据库，可以把数据库参数加到connect的语句里：

#conn=pymysql.connect(host='localhost',user='root',passwd='password',db='test',charset='utf8',port=3306)

#查看有哪些表

>>> cur.execute('show tables')

#fetchall是获得所有的查询结果

>>> tables_list=cur.fetchall()

>>> tables_list

(('user',), ('user2',), ('user3',), ('user4',), ('user5',), ('user6',), ('user7',))

#创建table

>>> cur.execute('create table user8(id varchar(10),name varchar(10))')

#如果习惯于每一个colmn单独一行，可以用'''代替'

>>> cur.execute('''create table user8(id varchar(10),

name varchar(10))''')

#查看表user,execute中的语句语法跟mysql中的一样

>>> cur.execute('select * from user')

>>> user_select_result=cur.fetchall()

>>> user_select_result

(('', 'Michael'), ('', 'ozil'), ('', 'Giroud'), ('', 'Henry'), ('Alexis', ''), ('Ramsey', ''), ('Walcott', ''))

>>> cur.execute('select * from user')

#fetchone只获得第一条查询结果

>>> user_select_result=cur.fetchone()

>>> user_select_result

('', 'Michael')

>>> cur.execute('select * from user')

#fetchmany(n)，可以获得n条查询结果

>>> user_select_result=cur.fetchmany(4)

>>> user_select_result

(('', 'Michael'), ('', 'ozil'), ('', 'Giroud'), ('', 'Henry'))

#插入数据，注意插入语句的插入参数一定要是变量，不能是直接一个set

>>> insert_value=('','gibbs')

>>> cur.execute('insert into user(id,name) values(%s,%s)',insert_value)

>>> cur.execute('select * from user')

>>> user_select_result=cur.fetchall()

>>> user_select_result

(('', 'Michael'), ('', 'ozil'), ('', 'Giroud'), ('', 'Henry'), ('', 'gibbs'), ('Alexis', ''), ('Ramsey', ''), ('Walcott', ''))

insert_value_list=[('','debucy'),('','cech')]

#插入多条数据，需要用executemany

>>> cur.executemany('insert into user(id,name) values(%s,%s)',insert_value_list)

>>> cur.execute('select * from user')

>>> user_select_result=cur.fetchall()

>>> user_select_result

(('', 'Michael'), ('', 'ozil'), ('', 'Giroud'), ('', 'Henry'), ('', 'debucy'), ('', 'gibbs'), ('', 'cech'), ('Alexis', ''), ('Ramsey', ''), ('Walcott', ''))

#只有conn.commit()后，对数据库的修改才会提交

>>> conn.commit()

>>> cur.execute('update user set name="Ozil" where id="11"')

>>> user_select_result=cur.fetchall()

>>> user_select_result

()

>>> cur.execute('select * from user')

>>> user_select_result=cur.fetchall()

>>> user_select_result

(('', 'Michael'), ('', 'Ozil'), ('', 'Giroud'), ('', 'Henry'), ('', 'debucy'), ('', 'gibbs'), ('', 'cech'), ('Alexis', ''), ('Ramsey', ''), ('Walcott', ''))

#修改后一定要comiit，不然删除、更新、添加的数据都不会被写进数据库中。

>>> conn.commit()

#最后要把cur和conn都关掉

>>> cur.close()

>>> conn.close()