Python数据库

连接Sqlite

import sqlite3
import traceback

try:
    with sqlite3.connect('test.db') as conn:
        print('Opened database successfully')
        cur=conn.cursor()
        conn.execute('Drop Table if exists company')
        
        sql='''
                 CREATE TABLE IF NOT EXISTS COMPANY
               (ID INTEGER  PRIMARY KEY       AUTOINCREMENT,
               NAME           TEXT    NOT NULL,
               AGE            INT     NOT NULL,
               ADDRESS        CHAR(50),
               SALARY         REAL);
        '''
        conn.execute(sql)
        print('Create Table successfully')
        conn.executemany('Insert into company(name,age,address,salary) values(?,?,?,?)',[('Paul', 32, 'California', 20000.00),
                          ('Allen', 25, 'Texas', 15000.00),
                          ('Teddy', 23, 'Norway', 20000.00),
                          ('Mark', 25, 'Rich-Mond ', 65000.00),
                          ('David', 27, 'Texas', 85000.00),
                          ('Kim', 22, 'South-Hall', 45000.00),
                          ('James', 24, 'Houston', 10000.00)])
        conn.execute('Insert into company(name,age,address,salary) values("mayixiao",22,"KM",1800)')
        conn.execute('Insert into company(name,age,address,salary) values(?,?,?,?)',('le',33,'YN',2000)) # 必须用一个元组包括起要传入的参数
        
        conn.commit()
        print('Insert successfully')
        
        sql='select * from company'
        result=conn.execute(sql)
        for x in result:
            print('-----------------------------------------------')
            print(x)
            print('+---------------------------------+')
            print('i\'m '+x[1]+',i\'m from '+x[3])
        
except sqlite3.Error as e:
    conn.rollback()
    print('sqlite3 Error:',e)
    traceback.print_exc()

连接Mysql

import pymysql

conn=pymysql.connect(host='127.0.0.1',user='root',password='123456',db='dlearning1')
cur=conn.cursor()
cur.execute('select * from account')
row=cur.fetchall()
for x in row:
    print(x)

连接Oracle

import cx_Oracle

db = cx_Oracle.connect('xxxx', 'xxxxx', '100.88.16.1111/orcl')
cursor = db.cursor()  # 创建游标
cursor.execute('select sysdate from dual')
result = cursor.fetchall()
print(result)
db.close()

连接MSSQL

import pymssql

db = pymssql.connect('host', 'usr', 'pwd', 'dbname')
cur=db.cursor()
cur.execute('select * from table')
result=cur.fetchall()
print(result)
db.close()

• pymssql存在一个问题，就是连接时传入的数据库名称不能是非ASCII码的字符，否则会出现编码出错，github上有该问题的解决方案；个人采用了其中的在创建连接对象时不传入database参数，在之后执行sql语句之前使用use databaseName来实现连接到某个数据库

db = pymssql.connect(host, usr, pwd)  # 只传入这3个参数
cursor=db.cursor()
cursor.execute(f'use {dbname}')  # 使用数据库

获取查询结果集的表头

• cursor对象的description属性包含当前查询结果的所有描述，其中就是每个字段的名称，即表头

cursor.execute(sql)

# execute之后
cursor.description  # 获取execute的结果的描述
# fetchall之前

result = cursor.fetchall()