【2022.06.23】python连接数据库
查有什么数据库
import pymysql
conn = pymysql.connect(host='',
user='root',
password='123456',
port=)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 这里括号留空,返回的是元组
cursor.execute('show databases;')
for item in cursor.fetchall():
print(type(item), item) # item 是 tuple 元组
cursor.close()
conn.close()
查某个数据库下有什么表
import pymysql
conn = pymysql.connect(host='',
user='root',
password='123456',
port=)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute('use Shanghai;') # 这里修改datebase名称
cursor.execute('show tables;')
for item in cursor.fetchall():
print(type(item), item)
cursor.close()
conn.close()
创建表
import pymysql
conn = pymysql.connect(host='127.0.0.1',
user='root',
password='123456',
port=3306,
database='blog')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
affect_rows = cursor.execute('''
create table `user_info` (
`id` bigint unsigned not null auto_increment,
`name` varchar(45) not null default '',
primary key (`id`)
) engine = InnoDB default charset = utf8mb4;
''')
print(affect_rows) # 输出0
cursor.close()
conn.close()
插入数据
import pymysql
conn = pymysql.connect(host='127.0.0.1',
user='root',
password='123456',
port=3306,
database='blog',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
affect_rows = cursor.executemany('INSERT INTO `user_info` (name) values(%(name)s);',
[
{'name': 'name005'},
{'name': 'name006'}
])
print('影响行数: ', affect_rows) # 输出 2
print('插入数据对应的主键id: ', cursor.lastrowid) # 这里只返回第一个数据的id
cursor.close()
conn.close()
插入datetime参数
import datetime
import pymysql
import datetime
conn = pymysql.connect(host='',
user='root',
password='123456',
port=,
database='Shanghai',
autocommit=True
)
print(datetime.datetime.now().strftime("%Y%m%d"))
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
affect_rows = cursor.executemany('INSERT INTO `ALL` (DATE, NAME, DATA, DATA_CHANGE) values(%(DATE)s, %(NAME)s, %(DATA)s, %(DATA_CHANGE)s);',
[
{'DATE': datetime.datetime.now(), 'NAME': 'name005', 'DATA': 44, 'DATA_CHANGE': 50},
{'DATE': datetime.datetime.now(), 'NAME': 'name006', 'DATA': 46, 'DATA_CHANGE': 52}
])
print('影响行数: ', affect_rows) # 输出 2
print('插入数据对应的主键id: ', cursor.lastrowid) # 这里只返回第一个数据的id
cursor.close()
conn.close()
参考链接
Python PyMySQL : 使用 insert 插入数据 - 乐天笔记 (letianbiji.com)
[python3实现往mysql中插入datetime类型的数据 - 云+社区 - 腾讯云 (tencent.com)](