While your immediate issue is resolved likely due to the raw cursor not retrieving all records from database whereas cursor.fetchall() or list(cursor) imports all rows to client side (Python), a slight memory footprint but may ensure rows import in script, I would be remiss to not encourage best practices in database design. As mentioned in comments, reconsider this
approach and normalize your entire database model for one-to-many or many-to-many relationships between tables as relational databases are intended to do. This way you avoid the overhead, maintenance, and querying challenges of 280+ identically structured tables. From your explanation, you sound like you need a distinct friends table with one-to-many links with workplaces
and posts table and any others, all connected to a friendid as foreign keys. Ultimately, you would create tables only once and populate data with append queries: -- CREATE TABLES
CREATE TABLE friends (
`friendid` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`first_name` TEXT, last_name TEXT,
`work` TEXT, `education` TEXT,
`current_city` TEXT, `phone_number` TEXT,
`dob` TEXT, `gender` TEXT, `sexual_orientation` TEXT,
`religion` TEXT, `relationship_status` TEXT, `about_me` TEXT);
CREATE TABLE workplaces (
`workplaceid` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`friendid` INTEGER,
`workplace` TEXT NOT NULL,
`city` TEXT, `state` TEXT);
CREATE TABLE posts (
`postid` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`friendid` INTEGER,
`text` TEXT NOT NULL,
`postdate` TEXT, `postlocation` TEXT);
-- POPULATE TABLES (adjust to actual tables/fields)
INSERT INTO friends (first_name, last_name)
SELECT SUBSTR(`name`, 1, INSTR(`name`, " ")) AS first_name,
SUBSTR(`name`, INSTR(`name`, " ")+1) AS last_name,
FROM fb_friends;
INSERT INTO workplaces (friendid, work, city)
SELECT f.friendid, w.work, w.city
FROM fb_work w INNER JOIN friends f ON w.name = f.first_name & ' ' & f.last_name;
INSERT INTO posts (friendid, `text`, postdate)
SELECT f.friendid, p.post_text, p.post_date
FROM fb_posts p INNER JOIN friends f ON p.name = f.first_name & ' ' & f.last_name;
Summary: in this tutorial, we will show you how to create tables in the SQLite database from the Python program using the sqlite3 module. To create a new table in an SQLite database from a Python program, you use the following steps: - First, create a
Connection object using the connect() function of the sqlite3 module. - Second, create a
Cursor object by calling the cursor() method of the Connection object.
- Third, pass the
CREATE TABLE statement to the execute() method of the Cursor object and execute this method.
For the demonstration, we will create two tables: projects and tasks as shown in the following database diagram: The following CREATE TABLE statements create these two tables:
-- projects table
CREATE TABLE IF NOT EXISTS projects (
id integer PRIMARY KEY,
name text NOT NULL,
begin_date text,
end_date text
);
-- tasks table
CREATE TABLE IF NOT EXISTS tasks (
id integer PRIMARY KEY,
name text NOT NULL,
priority integer,
project_id integer NOT NULL,
status_id integer NOT NULL,
begin_date text NOT NULL,
end_date text NOT NULL,
FOREIGN KEY (project_id) REFERENCES projects (id)
);
Code language: SQL (Structured Query Language) (sql)
Let’s see how to create new tables in Python. First, develop a function called create_connection() that returns a Connection object which represents an SQLite database specified by the database file parameter db_file. def create_connection(db_file):
""" create a database connection to the SQLite database
specified by db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
Code language: Python (python)
Second, develop a function named create_table() that accepts a Connection object and an SQL statement. Inside the function, we call the execute() method of the Cursor object to execute the CREATE TABLE statement. def create_table(conn, create_table_sql):
""" create a table from the create_table_sql statement
:param conn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
Code language: SQL (Structured Query Language) (sql)
Third, create a
main() function to create the projects and tasks tables. def main():
database = r"C:\sqlite\db\pythonsqlite.db"
sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
id integer PRIMARY KEY,
name text NOT NULL,
begin_date text,
end_date text
); """
sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
id integer PRIMARY KEY,
name text NOT NULL,
priority integer,
status_id integer NOT NULL,
project_id integer NOT NULL,
begin_date text NOT NULL,
end_date text NOT NULL,
FOREIGN KEY (project_id) REFERENCES projects (id)
);"""
# create a database connection
conn = create_connection(database)
# create tables
if conn is not None:
# create projects table
create_table(conn, sql_create_projects_table)
# create tasks table
create_table(conn, sql_create_tasks_table)
else:
print("Error! cannot create the database connection.")
Code language: Python (python)
Fourth, execute the main() function. if __name__ == '__main__':
main()
Code language: SQL (Structured Query Language) (sql)
Here is the full program: import sqlite3
from sqlite3 import Error
def create_connection(db_file):
""" create a database connection to the SQLite database
specified by db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
def create_table(conn, create_table_sql):
""" create a table from the create_table_sql statement
:param conn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def main():
database = r"C:\sqlite\db\pythonsqlite.db"
sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
id integer PRIMARY KEY,
name text NOT NULL,
begin_date text,
end_date text
); """
sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
id integer PRIMARY KEY,
name text NOT NULL,
priority integer,
status_id integer NOT NULL,
project_id integer NOT NULL,
begin_date text NOT NULL,
end_date text NOT NULL,
FOREIGN KEY (project_id) REFERENCES projects (id)
);"""
# create a database connection
conn = create_connection(database)
# create tables
if conn is not None:
# create projects table
create_table(conn, sql_create_projects_table)
# create tasks table
create_table(conn, sql_create_tasks_table)
else:
print("Error! cannot create the database connection.")
if __name__ == '__main__':
main()
Code language: Python (python)
Let’s verify if the program has created those tables successfully in the pythonsqlite.db database. First, launch the command line and connect to the pythonsqlite.db database: >sqlite3 c:\sqlite\db\pythonsqlite.db Then, use the .tables command to display the tables in the database. sqlite> .tables
projects tasks
Code language: CSS (css)
As you can see clearly from the output, we are having the projects and tasks tables in the pythonsqlite.db database. And the program works as expected. In this tutorial, you have learned how
to create new tables in the SQLite database using the execute() method of the Cursor object.
Was this tutorial helpful ?
How can I create multiple tables in SQLite database?
Now come to Android Studio and follow steps to create SQLite with multiple tables in Android.. Step 1: Create a new project in Android Studio.. Step 2: Creating UserModel class. ... . Step 3: Making DatabaseHelper class. ... . Step 4: Description of DatabaseHelper. ... . Step 5: Preparing lv_item.xml file. ... . Step 6: Preparing CustomAdapter class.. Can SQLite database have multiple tables?
For this purpose, two tables are created in our SQLite database (college. db). The students table has the same structure as given in the previous section; whereas the addresses table has st_id column which is mapped to id column in students table using foreign key constraint.
How do I create a SQLite table in Python?
SQLite Python: Creating Tables
First, create a Connection object using the connect() function of the sqlite3 module. Second, create a Cursor object by calling the cursor() method of the Connection object. Third, pass the CREATE TABLE statement to the execute() method of the Cursor object and execute this method.
How many tables can I create in SQLite?
Maximum Number Of Tables In A Join
SQLite does not support joins containing more than 64 tables.
| 
