Can I Add A Second SQL Database to this code and use both?

[Rapier]

I basically created a table like so:

import sqlite3

def create_table(db_name,sql):
    with sqlite3.connect(db_name) as db:
        cursor = db.cursor()
        cursor.execute(sql)
        db.commit()
        
if __name__ =="__main__":
    db_name = "MainTable.db"
    sql = """create table Info
             (Name text,
             Age integer,
             Password text,
             Username text,
             primary key(Username))"""
    create_table(db_name, sql)

and I have code that uses this to retrieve information but I also want to add another separate table to also retrieve information from. So far I'm trying to make it like so using a different definition but its not working:

import sqlite3

def create_table_2(db_name,sql):
    with sqlite3.connect(db_name) as db:
        cursor = db.cursor()
        cursor.execute(sql)
        db.commit()
        
if __name__ =="__Other__":
    db_name = "OtherTable.db"
    sql = """create table Add_Info
             (Username text,
             QuizName text,
             Grade text,
             Score integer,
             Difficulty text,
             primary key(Username))"""
    create_table_2(db_name, sql)

So I wanted to know if it's possible to use two different databases in the same code.

[mpd]

I honestly don't know why it isn't working and while I hate it when people do this to me, I have to ask: why are you using a second database file and not just adding the table to the existing database (MainTable.db)?

If this is one application, create both tables in the same database (file). If they're in the same file, you can make complex queries against both tables.

[buran]

@mpd - I think it is obvious OP is inexperienced, so I would guess this is the reason for the question and using incorrect approach with two db files.

[Rapier]

@mpd Can you please tell me how can you add two tables in the same database because the only reason i made a second database was because I couldn't figure that out.

[Larz60+]

Here's a a way to do it as a class:
f-string statements require python 3.6

import sqlite3


class MyApp:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.c = self.conn.cursor()

    def create_table(self, tablename, cols, pkey=None):
        newcol = True
        sqlstmt = f'CREATE TABLE {tablename} ('
        for col in cols:
            if newcol:
                sqlstmt = f'{sqlstmt}{col}'
                newcol = False
            else:
                sqlstmt = f'{sqlstmt}, {col}'
        if pkey is not None:
            newkey = True
            sqlstmt = f' {sqlstmt}, primary key ('
            for key in pkey:
                if newkey:
                    sqlstmt = f'{sqlstmt}{key}'
                    newkey = False
                else:
                    sqlstmt = f'{sqlstmt}, {key}'
            sqlstmt = f'{sqlstmt})'
        sqlstmt = f'{sqlstmt});'
        self.c.execute(sqlstmt)

    def create_tables(self):
        self.create_table(tablename='Info', cols=['Name text', 'Age integer', 'Password text',
                                                  'Username text'], pkey=['Username'])
        self.create_table(tablename='Add_Info', cols=['Username text', 'QuizName text',
                                                      'Grade text', 'Score integer', 'Difficulty text'],
                          pkey=['Username'])
        self.conn.commit()

def main():
    app = MyApp(db_name='MyDb.db')
    app.create_tables()

if __name__ == '__main__':
    main()

after running, sqlite interactive query:

Output:
λ sqlite3 MyDb.db                                                                                         
SQLite version 3.21.0 2017-10-24 18:55:49                                                                 
Enter ".help" for usage hints.                                                                            
sqlite> .schema Info                                                                                      
CREATE TABLE Info (Name text, Age integer, Password text, Username text, primary key (Username));         
sqlite> .schema Add_Info                                                                                  
CREATE TABLE Add_Info (Username text, QuizName text, Grade text, Score integer, Difficulty text, primary k
ey (Username));                                                                                           
sqlite> .quit                                                                                            

[mpd]

Larz60+ answer works fine. Personally I wouldn't do so much just to make some tables. I'd just do something like this:

def create_tables(dbname):
    try:
        db = sqlite3.connect(dbname)
        c = db.cursor()
        stmts = ["CREATE TABLE foo ...", "CREATE TABLE bar ..."]
        for s in stmts:
            c.execute(s)
        db.commit()
        db.close()
        return True
    except Exception as e: # I forget what SQLITE3 exceptions get raised
        return False

Since we're talking about databases, when you're making DB queries, be sure to use the "parameterized" form. "Parameterized queries" perform proper and safe string insertion and prevent security violations aka SQL Injection attacks.

So if you are adding a user to a table and get the name and age from a person (e.g., via a dialog box), you should do something like the following (i'm skipping the error handling):

def insert_user(username, age):
    db = sqlite3.connect(DB_NAME)
    c = db.cursor()
    c.execute("INSERT INTO users (name, age) VALUES (%s, %s)", (username, age))
    db.commit()
    db.close()

Notice that there is a comma between the query string and the arguments. This is not simple string formatting. The execute function will replace the %s's with the appropriate variables after doing proper data- sanitization. There's more information about it here: https://docs.python.org/3/library/sqlite3.html

It's extremely important to get into the habit of using parameterized queries.

[Larz60+]

However if done in a class, and from my example,

def create_tables()

is kept outside of the class,
whenever a new table is required, it's as simple as a call to the class method

classname.create_table(tablename='Info', cols=['Name text', 'Age integer', 'Password text','Username text'], pkey=['Username'])

[mpd]

However if done in a class, and from my example,

def create_tables()

is kept outside of the class,
whenever a new table is required, it's as simple as a call to the class method

classname.create_table(tablename='Info', cols=['Name text', 'Age integer', 'Password text','Username text'], pkey=['Username'])

The aspect of your solution that I dislike is the complicated method of putting together a pretty simple CREATE TABLE statement when you have all the parts of the string right there. Why not just take the table statement as an argument?

[Larz60+]

Keep the work in the black box.

[mpd]

Keep the work in the black box.

I agree. Now we just have to decide what's in and out of the box.