数据库学习02

‘’’
1、连接Mysql
2、执行SQL
3、查询数据
4、事物处理
5、生产环境使用
‘’’

import pymysql

connection = pymysql.connect(host = 'localhost',
                             port = 3306,
                             user = 'root',
                             password = '123456',
                             db = 'test',
                             charset = 'utf8'
)
1
2
3
4
5
6
7
8
9

‘’’
参数解释：host，数据库服务器地址，默认为localhost；
user，用户名，默认为当前程序运行用户；
password，登陆密码
db，默认操作的数据路；
port，数据库端口；
charset，数据库编码
‘’’

#执行sql，获取游标

cursor = connection.cursor()
#创建数据表
effect_row = cursor.execute('''
CREATE TABLE users(
name varchar(32) NOT NULL,
age int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY(name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
''')

#插入数据（元组或列表）
effect_row = cursor.execute('INSERT INTO users (name,age) VALUES (%s,%s)',('mary',18))
#插入数据（字典）
info = {'name':'fake','age':15}
effect_row = cursor.execute('INSERT INTO users (name,age) VALUES (%(name)s,%(age)s)',info
                            )
connection.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

#查询不需要使用commit方法，更新添加需要commit

data = {
    'user_id':1,
    'question_id':2,
    'answer':'答案',
    'answer_detail':'one answer',
    'review': 'jiangshidianping'
}
sql = 'INSERT INTO answer(user_id,question_id,answer,answer_detail,review) VALUES (%(user_id)s,%(question_id)s,%(answer)s,%(answer_detail)s,%(review)s)'
res = cursor.execute(sql,data)
connection.commit()
print(res)
1
2
3
4
5
6
7
8
9
10
11

#cursor.executemany(sql,args)批量执行sql

effect_row = cursor.executemany(
    'INSERT INTO users (name,age) VALUES (%s,%s) ON DUPLICATE KEY UPDATE age = VALUES(age)',[
        ('hello',13),
        ('fake',28),
    ]
)
1
2
3
4
5
6

connection.commit()
1

#查询语句

cursor.execute('SELECT * FROM users')
#获取单条数据
print('one sql',cursor.fetchone())

#获取前N条数据
print('second sql', cursor.fetchmany(2))

#获取所有数据
print('all sql',cursor.fetchall())
1
2
3
4
5
6
7
8
9

#事务处理

开启事务 connection.begin()
提交修改 connection.commit()
回滚事务 connection.rollback()
1
2
3

#常用的封装操作
https://github.com/ayuliao/mysqldb