python之SQLite连接数据库

SQLite

连接数据库

# 1.连接数据库
conn = sqlite3.connect("test.db")     # 打开或创建数据库文件

print("成功打开数据库")

创建数据表

# 1.连接数据库
conn = sqlite3.connect("test.db")     # 打开或创建数据库文件

print("成功打开数据库")

# 2.创建数据表
c = conn.cursor()  # 获取游标

sql = '''
    create table company
        (id int primary key not null,
        name text not null,
        age int not null,
        address char(50),
        salary real);       
'''

c.execute(sql)   # 执行sql语句
conn.commit()    # 提交数据库操作
conn.close()     # 关闭数据库链接

print("成功建表")

插入数据

import sqlite3


# 1.连接数据库
conn = sqlite3.connect("test.db")     # 打开或创建数据库文件

print("成功打开数据库")

# 2.创建数据表
c = conn.cursor()  # 获取游标

sql1 = '''
    insert into company (id,name,age,address,salary)
    values(1,"张三" ,"19","西安","10000")    
'''

sql2 = '''
    insert into company (id,name,age,address,salary)
    values(2,"bkys" ,"19","西安","1000000")    
'''

c.execute(sql1)   # 执行sql语句
c.execute(sql2)
conn.commit()    # 提交数据库操作
conn.close()     # 关闭数据库链接

print("插入数据完毕")

查询数据

# 1.连接数据库
conn = sqlite3.connect("test.db")     # 打开或创建数据库文件

print("成功打开数据库")

# 2.创建数据表
c = conn.cursor()  # 获取游标

sql = "select id,name,address,salary from company"

cursor = c.execute(sql)

for row in cursor:
    print("id = ", row[0])
    print("name = ", row[1])
    print("address = ", row[2])
    print("salary = ", row[3], "\n")

conn.close()     # 关闭数据库链接

print("查询完毕")

def saveData2DB(datalist,dbpath):
            init_db(dbpath)
            conn =sqlite3.connect(dbpath)
            cur = conn.cursor()

            for data in datalist:
                for index in range(len(data)):
                    data[index] = '"' + data[index] + '"'
                sql = '''
                        insert into movie250 (
                        info_link, pic_link, cname, ename, score, rated, introduction, info) 
                        values(%s)''' % ",".join(data)
                cur.execute(sql)
                conn.commit()
            cur.close()
            conn.close()


def init_db(dbpath):
    sql = '''
         create table movie250
         (
         id integer primary key autoincrement,
         info_link text,
         pic_link text,
         cname varchar ,
         ename varchar ,
         score numeric ,
         rated numeric ,
         instroduction text,
         info text
         )      
    '''
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    conn.close()