A happy journey of simple Python: SQLite3 of Python basic syntax

Defonds 2020-11-13 04:49:32
happy journey simple python sqlite3



SQLite It's a lightweight disk based database , It is not like MySQL That requires a separate server , Its data is direct persistent storage , Its database is actually a file .
Like any other relational database , We will learn about Python Yes sqlite3 Database connection object creation 、 Create tables in the database 、 Insert a record... Into the table 、 Query the data in the table based on clauses 、 Update data based on clauses 、 Delete some or the whole table data and other related skills .

1. Create connection objects

Python Built in sqlite3, And provide sqlite3 library . So we don't need to install anything 、 It can be used directly .
To create a sqlite The connection object of , have access to sqlite3.connect() function . The syntax of this function is as follows :

conn = sqlite3.connect('dbname.db')

among ,connect() The function reads a string as the database name and returns a sqlite3.Connection Class object .
If the database already exists , It will only return one Connection object , Otherwise, the database will be created and a new database will be returned Connection object .

1.1. Use sqlite3 Create a Connection Examples of objects

In this case , We will create a connection to sqlite be known as mysqlite.db Database Connection object .

import sqlite3
conn = sqlite3.connect('mysqlite.db')

In the use of sqlite3 You need to import any function of sqlite3 library .
Of course , You can also in memory (RAM) Create a database , Just create Connection When the object will be :memory: As a parameter to sqlite3.connect() that will do .

import sqlite3
conn = sqlite3.connect(':memory:')

To create in your sqlite If you operate on the database , You have to give Connection Object to create a cursor .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

2. stay sqlite3 Database create table

You can sqlite3 Create one or more tables in the database .
In this section , We will learn how to use Python stay sqlite3 Create a new table in the database .
Use Python sqlie3 To create a table, follow these steps :

  1. Create a connection to sqlite3 Database Connection object .
  2. One Connection Object's cursor .
  3. take CREATE Query to sqlite3.execute() To create a new table .

2.1. Use sqlite3 new table

In this example , We will create a new one called mysqlite.db The database of , And create a new one called students Table of .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Examples of building tables .png
After executing the program , A group called students The new table will be in the database mysqlite.db Created in . But if you execute the program again , You will get the following wrong results :
 Execute the table creation statement again .jpg
In order not to be disturbed by whether the table already exists , We can refer to the following section for example , A new table is created only if the table does not exist .

2.2. A new table is created only if the table does not exist

In the query statement , We can define it as creating a new table only when the table does not exist . You can use... Before the table name in the query statement IF NOT EXISTS To create a new table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# Create the table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 A new table is created only if the table does not exist .jpg
You can see , Although we have created the table before executing the program students, But I was not disturbed by the error in the previous table .

2.3. Summary

In this section , We learned how to be in sqlite3 Create a new table in the database . Besides , We also learned about how to create a new table only when the table does not exist .

3. See if the table already exists

Before executing a query statement on a table , You can check whether the table already exists in sqlite3 database .
To check if a table already exists in sqlite3 database , You can go from the watch sqlite_master Query whether there is a table name matching your table name .
The relevant syntax is as follows :

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

among ,table_name Need to replace with the table name you want to query .
In the results of this query, you can check whether there are some lines . If there's a line of results , Then the table already exists .

3.1. Check whether the table already exists in sqlite3

In the example in the previous section, we have created a new one called students Table of . Now? , We will program to check if the table already exists .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of the tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name='students' ''')
# if the count is 1, then table exists
if c.fetchone()[0]==1:
print('Table students exists')
else:
print('Table students not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Check whether the table already exists in sqlite3.png

3.2. Check whether the checklist already exists in sqlite3 ( A scene that doesn't exist )

In this example, we will examine a negative scenario , It's called students1 The table of does not exist in sqlite3 Database scenario .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' and name='students1' ''')
# if the count is 1, then exists
if c.fetchone()[0]==1:
print('Table students1 exists')
else:
print('Table students1 does not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Check whether the checklist already exists in sqlite3 ( A scene that doesn't exist ).png

3.3. Check if the table exists in memory (RAM)

If you want to check the memory (RAM) In the table , have access to sqlite_temp_master Put... In the above example sqlite_master Replace .

3.4. Summary

In this example, we learned to check whether the specified table already exists in sqlite3 Knowledge of database .

4. towards sqlite3 Insert row in table

You can go to sqlite3 Insert one or more rows... Into the table .
To insert a row of data into sqlite3 surface , Follow these steps .

  1. Create a connection to sqlite3 Database connection .
  2. Get a cursor for the connection .
  3. Check if the table already exists , If it doesn't exist, create a new one .
  4. If the table already exists , Using cursors execute() Method , By way of SQL The insert query is passed to the method to insert a new row .

You can go through cursor.lastrowid() To check if the new row is inserted successfully .

4.1. Insert a line into sqlite3 surface

In the next example , If the table does not exist, we will create a new table , And then use INSERT INTO Query to insert a record into the table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Alex', 8)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Insert a line into sqlite3 surface .jpg

4.2. Insert new row to sqlite3 Table and check if the insert was successful

In most scenarios , We need to check that INSERT INTO Query whether the insert is successful .
Do that , We can check by sqlite3 Of the last row of records inserted by the cursor id, If it's time to id No 0, Then we can confirm that the insertion has been successful .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Glen', 8)''')
print(c.lastrowid)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Insert new row to sqlite3 Table and check if the insert was successful
If the output is not zero , So prove that your insertion did work . If you get a zero , So you need to debug your program .

4.3. Summary

In this section , We learned how to sqlite3 Insert new row in table .

5. from sqlite3 Query rows in the table

From you to sqlite3 To query data in a database table, follow these steps :

  1. Create a connection to sqlite Database Connection object .
  2. Create a new cursor for this connection .
  3. take SELECT FROM Query to sqlite3.execute() Method and execution .

5.1. from sqlite3 Read data in table

The following example will read sqlite3 Database table students All the lines in .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students;''')
rows = c.fetchall()
for row in rows:
print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 from sqlite3 Read data in table .png
You can see that the previously inserted test data has been printed out . Of course , You can also modify SELECT FROM Query to filter data 、 Sort or transform .

5.2. Use WHERE Clause from sqlite3 Read lines in

Next is in SELECT FROM Use in query WHERE Example of filtering query lines by clauses .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students WHERE name="Alex";''')
rows = c.fetchall()
for row in rows:
print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Use WHERE Clause from sqlite3 Read lines in .png

5.3. Summary

In this section we learned about how to query records from a table .

6. Insert multiple lines to sqlite3 surface

In section four , We learned how to insert a single row into a table . In this section, we'll learn how to use a single INSERT INTO Query inserts multiple rows of data into the table .
The steps to insert multiple rows of records into a table are as follows :

  1. Ready to connect to the database Connection Object and get a cursor .
  2. Store the record to be inserted in a sheet list in .
  3. Insert the list As a parameter to executemany() Method and execution .

6.1. Insert multiple lines to sqlite surface

In the next example , We'll go to the students Insert three more pieces of data into the table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# records or rows in a list
records = [(1, 'Alen', 8),
(2, 'Elliot', 9),
(3, 'Bob', 7)]
# insert multiple records in a single query
c.executemany('INSERT INTO students VALUES(?,?,?);', records)
print('We have inserted', c.rowcount, 'records to the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Insert multiple lines to sqlite surface .jpg
We print out cursor.rowcount To confirm that the query statement successfully inserted three records .

6.2. Summary

In this section , We learned how to insert multiple rows of records into a single query statement sqlite3 Table related knowledge .

7. Delete sqlite3 All records in the table

To delete sqlite3 All records in the table , perform DELETE FROM Inquire about . The detailed steps are as follows :

  1. Create a new one sqlite3 The connection of .
  2. Get the cursor of the connection .
  3. perform DELETE FROM table Inquire about .

7.1. Delete sqlite3 The rows in the table

In the following example , We will learn how to use DELETE FROM table The query statement will sqlite3 Delete all the records in the table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# delete all rows from table
c.execute('DELETE FROM students;')
print('We have deleted', c.rowcount, 'records from the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output :
 Delete sqlite3 The rows in the table .png
you 're right , We inserted a total of 5 Data , Now it's all deleted .

7.2. Summary

In this section , We learned about deleting sqlite3 All the relevant knowledge recorded in the table .

Reference material

版权声明
本文为[Defonds]所创,转载请带上原文链接,感谢

  1. 利用Python爬虫获取招聘网站职位信息
  2. Using Python crawler to obtain job information of recruitment website
  3. Several highly rated Python libraries arrow, jsonpath, psutil and tenacity are recommended
  4. Python装饰器
  5. Python实现LDAP认证
  6. Python decorator
  7. Implementing LDAP authentication with Python
  8. Vscode configures Python development environment!
  9. In Python, how dare you say you can't log module? ️
  10. 我收藏的有关Python的电子书和资料
  11. python 中 lambda的一些tips
  12. python中字典的一些tips
  13. python 用生成器生成斐波那契数列
  14. python脚本转pyc踩了个坑。。。
  15. My collection of e-books and materials about Python
  16. Some tips of lambda in Python
  17. Some tips of dictionary in Python
  18. Using Python generator to generate Fibonacci sequence
  19. The conversion of Python script to PyC stepped on a pit...
  20. Python游戏开发,pygame模块,Python实现扫雷小游戏
  21. Python game development, pyGame module, python implementation of minesweeping games
  22. Python实用工具,email模块,Python实现邮件远程控制自己电脑
  23. Python utility, email module, python realizes mail remote control of its own computer
  24. 毫无头绪的自学Python,你可能连门槛都摸不到!【最佳学习路线】
  25. Python读取二进制文件代码方法解析
  26. Python字典的实现原理
  27. Without a clue, you may not even touch the threshold【 Best learning route]
  28. Parsing method of Python reading binary file code
  29. Implementation principle of Python dictionary
  30. You must know the function of pandas to parse JSON data - JSON_ normalize()
  31. Python实用案例,私人定制,Python自动化生成爱豆专属2021日历
  32. Python practical case, private customization, python automatic generation of Adu exclusive 2021 calendar
  33. 《Python实例》震惊了,用Python这么简单实现了聊天系统的脏话,广告检测
  34. "Python instance" was shocked and realized the dirty words and advertisement detection of the chat system in Python
  35. Convolutional neural network processing sequence for Python deep learning
  36. Python data structure and algorithm (1) -- enum type enum
  37. 超全大厂算法岗百问百答(推荐系统/机器学习/深度学习/C++/Spark/python)
  38. 【Python进阶】你真的明白NumPy中的ndarray吗?
  39. All questions and answers for algorithm posts of super large factories (recommended system / machine learning / deep learning / C + + / spark / Python)
  40. [advanced Python] do you really understand ndarray in numpy?
  41. 【Python进阶】Python进阶专栏栏主自述:不忘初心,砥砺前行
  42. [advanced Python] Python advanced column main readme: never forget the original intention and forge ahead
  43. python垃圾回收和缓存管理
  44. java调用Python程序
  45. java调用Python程序
  46. Python常用函数有哪些?Python基础入门课程
  47. Python garbage collection and cache management
  48. Java calling Python program
  49. Java calling Python program
  50. What functions are commonly used in Python? Introduction to Python Basics
  51. Python basic knowledge
  52. Anaconda5.2 安装 Python 库(MySQLdb)的方法
  53. Python实现对脑电数据情绪分析
  54. Anaconda 5.2 method of installing Python Library (mysqldb)
  55. Python implements emotion analysis of EEG data
  56. Master some advanced usage of Python in 30 seconds, which makes others envy it
  57. python爬取百度图片并对图片做一系列处理
  58. Python crawls Baidu pictures and does a series of processing on them
  59. python链接mysql数据库
  60. Python link MySQL database