代码之家 › 专栏 › 技术社区 › Flamenco33

如何从多个数据库访问多个记录?

sqlite database sql python

Flamenco33 · 技术社区 · 3 年前

我想从tblUser中检索用户名记录,从tblResults中检索链接记录。

import sqlite3

connUsers = sqlite3.connect("Users.db")
users = connUsers.cursor()

def create_user_tbl():
      try:
        users.execute(
        """CREATE TABLE tblUser (
          UserID        INTEGER, 
          Username      TEXT, 
          Password      TEXT, 
    
          PRIMARY KEY   (UserID) )""")
        
        tblUser = [
          (1, 'Argon456', 'Fir3Tru0k'), (2, 'Max9', 'Thrsgdp0fnse'), (3, 'Steven', '1234')] 
        users.executemany("INSERT INTO tblUser VALUES (?, ?, ?)", tblUser); print("Database created")
      except: print("Database table already exists")
    
def create_results_tbl():
      try:
        users.execute(
        """CREATE TABLE tblResults (
          ResultsID   INTEGER, 
          Title       TEXT, 
          Location    TEXT,
          Link        TEXT,
          
          PRIMARY KEY (ResultsID) )""")
        
        tblResults = [
          (1, 'Business', 'Ayelsbury', 'www.link.com'), (2, 'Apprenticeship', 'Ealing', 'https://ealingpress.co.uk')]
        users.executemany("INSERT INTO tblResults VALUES (?, ?, ?, ?)", tblResults); print("Database created")
      except: print("Database table already exists")

for row in users.execute("SELECT A.Username, B.Link FROM A INNER JOIN B ON A.UserID = B.ResultsID;"):
  print(row)
users.commit()
users.close()

我相信我的SELECT语句是正确的,但我无法测试它,因为我不知道如何执行这个命令。要从不同的表中访问多个记录,我需要为每个表创建多个连接吗。数据库文件?如果不是,我会通过SELECT语句中的第一个表执行命令吗?

0 回复 | 直到 3 年前

seimen 3 年前

import sqlite3

connection = sqlite3.connect("Users.db")
cursor = connection.cursor()


def create_user_tbl():
    connection.execute(
        """CREATE TABLE tblUser (
          UserID        INTEGER, 
          Username      TEXT, 
          Password      TEXT, 

          PRIMARY KEY   (UserID) )""")
    print("Database created")

def populate_user():
        users = [
            (1, 'Argon456', 'Fir3Tru0k'),
            (2, 'Max9', 'Thrsgdp0fnse'),
            (3, 'Steven', '1234')
        ]

        connection.executemany("INSERT INTO tblUser VALUES (?, ?, ?)", users)

def create_results_tbl():
    connection.execute(
        """CREATE TABLE tblResults (
          ResultsID   INTEGER, 
          Title       TEXT, 
          Location    TEXT,
          Link        TEXT,

          PRIMARY KEY (ResultsID) )""")

def populate_results():
    results = [
        (1, 'Business', 'Ayelsbury', 'www.link.com'),
        (2, 'Apprenticeship', 'Ealing', 'https://ealingpress.co.uk')
    ]
    connection.executemany("INSERT INTO tblResults VALUES (?, ?, ?, ?)", results);


if __name__ == "__main__":
    # create_user_tbl()
    # create_results_tbl()
    # populate_user()
    # populate_results()
    # connection.commit()

    for row in connection.execute("SELECT tblUser.Username, tblResults.Link FROM tblUser INNER JOIN tblResults ON tblUser.UserID = tblResults.ResultsID;"):
        print(row)

印刷品

('Argon456', 'www.link.com')
('Max9', 'https://ealingpress.co.uk')