Python SQLite 连接
当你把数据分到两个表中时,你可以使用Join从这两个表中获取合并记录。
例子
假设我们创建了一个名为CRICKETERS的表,使用下面的查询 –
sqlite> CREATE TABLE CRICKETERS (
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Age int,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255)
);
sqlite>
让我们再创建一个表OdiStats,描述CRICKETERS表中每个球员的单日板球统计数据。
sqlite> CREATE TABLE ODIStats (
First_Name VARCHAR(255),
Matches INT,
Runs INT,
AVG FLOAT,
Centuries INT,
HalfCenturies INT
);
sqlite>
下面的语句检索了这两个表中的数据组合—-。
sqlite> SELECT
Cricketers.First_Name, Cricketers.Last_Name, Cricketers.Country,
OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies
from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name;
First_Name Last_Name Country Matches Runs Centuries HalfCenturies
---------- ---------- ------- ---------- ------------- ---------- ----------
Shikhar Dhawan Indi 133 5518 17 27
Jonathan Trott Sout 68 2819 4 22
Kumara Sangakkara Sril 404 14234 25 93
Virat Kohli Indi 239 11520 43 54
Rohit Sharma Indi 218 8686 24 42
sqlite>
使用python的连接子句
以下是SQLite的例子,演示了使用python的JOIN子句-
import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving data
sql = '''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID'''
#Executing the query
cursor.execute(sql)
#Fetching 1st row from the table
result = cursor.fetchall();
print(result)
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()
输出
[('Ramya', 'Rama priya', 27, 'F', 9000.0, 101, 101, 'Krishna@mymail.com', 'Hyderabad'),
('Vinay', 'Battacharya', 20, 'M', 6000.0, 102, 102,'Raja@mymail.com', 'Vishakhapatnam'),
('Sharukh', 'Sheik', 25, 'M', 8300.0, 103, 103, 'Krishna@mymail.com', 'Pune'),
('Sarmista', 'Sharma', 26, 'F', 10000.0, 104, 104, 'Raja@mymail.com', 'Mumbai')]