【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)](