xxxxxxxxxx
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
xxxxxxxxxx
import sqlite3
# Create a database and open the database.
# If the database already exists just opens the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Create a users table if the table does not exists
c.execute('''CREATE TABLE IF NOT EXISTS users(name TEXT, age INTEGER)''')
# commit changes and close database connect
conn.commit()
conn.close()
# Insert values with parameter queries to prevent sql injections
conn = sqlite3.connect('users.db')
c = conn.cursor()
name = "John"
number = 20
c.execute("INSERT INTO users VALUES (?, ?)", (name, number))
# commit changes and close database connect
conn.commit()
conn.close()
# Insert many values with parameter queries to prevent sql injections
users = [
("James", 10),
("Smith", 21),
("Jerome", 11)
]
conn = sqlite3.connect('users.db')
c = conn.cursor()
c.executemany("INSERT INTO users VALUES (?, ?)", users)
# commit changes and close database connect
conn.commit()
conn.close()
# Print values from table
conn = sqlite3.connect('users.db')
c = conn.cursor()
for row in c.execute('SELECT * FROM users'):
print(row)
# list comprehension to store all names and ages in separate lists
names = [row[0] for row in c.execute('SELECT * FROM users')]
ages = [row[1] for row in c.execute('SELECT * FROM users')]
# list comprehension store all names and ages in a list
combined = [f"Name: {row[0]} - Age: {row[1]}" for row in c.execute('SELECT * FROM users')]
# print names and ages list
print(names, ages)
# print name and ages command list
print(combined)
# close connection
conn.close()
# Print all rows in a list of tuples using fetchall
conn = sqlite3.connect('users.db')
c = conn.cursor()
result = c.execute("SELECT * FROM users")
print(result.fetchall())
conn.close()
# output
# ('John', 20)
# ('James', 10)
# ('Smith', 21)
# ('Jerome', 11)
# ['John', 'James', 'Smith', 'Jerome'] [20, 10, 21, 11]
# ['Name: John - Age: 20', 'Name: James - Age: 10', 'Name: Smith - Age: 21', 'Name: Jerome - Age: 11']
# [('John', 20), ('James', 10), ('Smith', 21), ('Jerome', 11)
xxxxxxxxxx
import sqlite3 as lite
import sys
try:
con = lite.connect('products.db')
cur = con.cursor()
cur.execute("CREATE TABLE drinks(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Price REAL)")
cur.execute("CREATE TABLE fruits(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Price REAL)")
con.commit()
except e:
if con:
con.rollback()
print("Unexpected error %s:" % e.args[0])
sys.exit(1)
finally:
if con:
con.close()
xxxxxxxxxx
# You don't need to install anything to use SQLite in Python.
# SQLite is included in the Python standard library, so you can start using it immediately.
# Here's an example of how to create a SQLite database and execute a simple query:
import sqlite3
# Create a connection to the database (if it doesn't exist, it will be created)
conn = sqlite3.connect('example.db')
# Create a cursor object to interact with the database
cursor = conn.cursor()
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL
)
''')
# Insert some data into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Charlie', 35)")
# Execute a query
cursor.execute("SELECT * FROM users")
# Fetch all the rows returned by the query
rows = cursor.fetchall()
# Print the data
for row in rows:
print('ID:', row[0])
print('Name:', row[1])
print('Age:', row[2])
# Don't forget to commit changes and close the connection when done
conn.commit()
conn.close()
xxxxxxxxxx
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)
except Error as e:
print(e)
return conn
def create_project(conn, project):
"""
Create a new project into the projects table
:param conn:
:param project:
:return: project id
"""
sql = ''' INSERT INTO projects(name,begin_date,end_date)
VALUES(?,?,?) '''
cur = conn.cursor()
cur.execute(sql, project)
conn.commit()
return cur.lastrowid
def create_task(conn, task):
"""
Create a new task
:param conn:
:param task:
:return:
"""
sql = ''' INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
VALUES(?,?,?,?,?,?) '''
cur = conn.cursor()
cur.execute(sql, task)
conn.commit()
return cur.lastrowid
def main():
database = r"C:\sqlite\db\pythonsqlite.db"
# create a database connection
conn = create_connection(database)
with conn:
# create a new project
project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30');
project_id = create_project(conn, project)
# tasks
task_1 = ('Analyze the requirements of the app', 1, 1, project_id, '2015-01-01', '2015-01-02')
task_2 = ('Confirm with user about the top requirements', 1, 1, project_id, '2015-01-03', '2015-01-05')
# create tasks
create_task(conn, task_1)
create_task(conn, task_2)
if __name__ == '__main__':
main()
Code language: Python (python)
xxxxxxxxxx
import sqlite3
con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()
buffer = ""
print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")
while True:
line = input()
if line == "":
break
buffer += line
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
cur.execute(buffer)
if buffer.lstrip().upper().startswith("SELECT"):
print(cur.fetchall())
except sqlite3.Error as e:
print("An error occurred:", e.args[0])
buffer = ""
con.close()