Python PostgreSQL - 连接
将数据分成两个表后,可以使用连接从这两个表中获取组合记录。
示例
假设我们创建了一个名为 CRICKETERS 的表,并在其中插入了 5 条记录,如下所示 −
postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); postgres=# insert into CRICKETERS values ( 'Shikhar', 'Dhawan', 33, 'Delhi', 'India' ); postgres=# insert into CRICKETERS values ( 'Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica' ); postgres=# insert into CRICKETERS values ( 'Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka' ); postgres=# insert into CRICKETERS values ( 'Virat', 'Kohli', 30, 'Delhi', 'India' ); postgres=# insert into CRICKETERS values ( 'Rohit', 'Sharma', 32, 'Nagpur', 'India' );
并且,如果我们创建了另一个名为 OdiStats 的表,并在其中插入 5 条记录,如下所示 −
postgres=# CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); postgres=# insert into OdiStats values ('Shikhar', 133, 5518, 44.5, 17, 27); postgres=# insert into OdiStats values ('Jonathan', 68, 2819, 51.25, 4, 22); postgres=# insert into OdiStats values ('Kumara', 404, 14234, 41.99, 25, 93); postgres=# insert into OdiStats values ('Virat', 239, 11520, 60.31, 43, 54); postgres=# insert into OdiStats values ('Rohit', 218, 8686, 48.53, 24, 42);
以下语句检索结合这两个表中的值的数据 −
postgres=# 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 | India | 133 | 5518 | 17 | 27 Jonathan | Trott | SouthAfrica | 68 | 2819 | 4 | 22 Kumara | Sangakkara | Srilanka | 404 | 14234 | 25 | 93 Virat | Kohli | India | 239 | 11520 | 43 | 54 Rohit | Sharma | India | 218 | 8686 | 24 | 42 (5 rows) postgres=#
使用 Python 进行连接
将数据分成两个表后,可以使用连接从这两个表中获取组合记录。
示例
以下 Python 程序演示了 JOIN 子句的用法−
import psycopg2 #建立连接 conn = psycopg2.connect( database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432' ) #设置自动提交为 false conn.autocommit = True #使用 cursor() 方法创建游标对象 cursor = conn.cursor() #检索单行 sql = '''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID''' #执行查询 cursor.execute(sql) #从表中获取第一行 result = cursor.fetchall(); print(result) #在数据库中提交你的更改 conn.commit() #关闭连接 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')]