Sqlite3 Cheatsheet For Python 3 GitHub [PDF]

Citation preview

Instantly share code, notes, and snippets. 7aman / sqlite3-in-python.py Created 2 years ago



Code



Revisions



1



Stars



10



Forks



5



Download ZIP



sqlite3 cheatsheet for python 3 sqlite3-in-python.py 1



#!/usr/bin/env python3



2 3



'''



4



Thanks to Andres Torres



5



Source: https://www.pythoncentral.io/introduction-to-sqlite-in-python/



6



'''



7 8



import sqlite3



9 10



# Create a database in RAM



11



# db = sqlite3.connect(':memory:')



12 13



# Creates or opens a file called mydb with a SQLite3 DB



14



db = sqlite3.connect('db.sqlite3')



15 16



##########



17



# CREATE #



18



##########



19



cursor = db.cursor()



20



cursor.execute('''



21



CREATE TABLE IF NOT EXISTS users(



22



id INTEGER PRIMARY KEY,



23



name TEXT,



24



phone TEXT,



25



email TEXT,



26



password TEXT



27



)



28



''')



29



db.commit()



30 31



https://gist.github.com/7aman/23678d233fa50900cc70ebfef2fd1f94



19/01/21, 2:46 PM Page 1 of 6



32



##########



33



# INSERT #



34



##########



35



'''



36



If you need values from Python variables it is recommended to use the "?" placeholder.



37



Never use string operations or concatenation to make your queries because is very insecur



38



'''



39



cursor = db.cursor()



40



name = 'Andres'



41



phone = '3366858'



42



email = '[email protected]'



43



password = '12345'



44



cursor.execute('''INSERT INTO users(name, phone, email, password)



45



VALUES(?,?,?,?)''', (name,phone, email, password))



46



db.commit()



47



'''



48



The values of the Python variables are passed inside a tuple.



49



Another way to do this is passing a dictionary using the ":keyname" placeholder:



50



'''



51



cursor = db.cursor()



52



cursor.execute('''INSERT INTO users(name, phone, email, password)



53



VALUES(:name,:phone, :email, :password)''',



54



{'name':name, 'phone':phone, 'email':email, 'password':password



55



db.commit()



56 57



# If you need to insert several users use executemany and a list with the tuples:



58



users = [('a','1', '[email protected]', 'a1'),



59



('b','2', '[email protected]', 'b1'),



60



('c','3', '[email protected]', 'c1'),



61



('c','3', '[email protected]', 'c1')]



62



cursor.executemany(''' INSERT INTO users(name, phone, email, password) VALUES(?,?,?,?)'''



63



db.commit()



64 65 66



# ????



67



# If you need to get the id of the row you just inserted use lastrowid:



68



id = cursor.lastrowid



69



print('Last row id: %d' % id)



70 71 72



##########



73



# SELECT #



74



##########



75



# To retrieve data, execute the query against the cursor object



76



# and then use fetchone() to retrieve a single row or fetchall() to retrieve all the rows



https://gist.github.com/7aman/23678d233fa50900cc70ebfef2fd1f94



19/01/21, 2:46 PM Page 2 of 6



77 78



cursor.execute('''SELECT name, email, phone FROM users''')



79



user1 = cursor.fetchone() #retrieve the first row



80



print(user1[0])



81



all_rows = cursor.fetchall()



82



for row in all_rows:



83



# row[0] returns the first column in the query (name), row[1] returns email column.



84



print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))



85



# The cursor object works as an iterator, invoking fetchall() automatically:



86



cursor.execute('''SELECT name, email, phone FROM users''')



87



for row in cursor:



88



print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))



89 90



# To retrive data with conditions, use again the "?" placeholder:



91



user_id = 3



92



cursor.execute('''SELECT name, email, phone FROM users WHERE id=?''', (user_id,))



93



user = cursor.fetchone()



94



db.commit()



95 96



##########



97



# UPDATE #



98



##########



99



# The procedure to update data is the same as inserting data:



100



newphone = '3113093164'



101



userid = 1



102



cursor.execute('''UPDATE users SET phone = ? WHERE id = ? ''', (newphone, userid



103



db.commit()



104 105



##########



106



# DELETE #



107



##########



108



# The procedure to delete data is the same as inserting data:



109



delete_userid = 2



110



cursor.execute('''DELETE FROM users WHERE id = ? ''', (delete_userid,))



111



db.commit()



112 113 114 115 116 117



### About commit() and rollback():



118



'''



119



Using SQLite Transactions:



120



Transactions are an useful property of database systems.



https://gist.github.com/7aman/23678d233fa50900cc70ebfef2fd1f94



19/01/21, 2:46 PM Page 3 of 6



121



It ensures the atomicity of the Database.



122



Use commit to save the changes.



123



Or rollback to roll back any change to the database since the last call to commit:



124



'''



125



cursor.execute('''UPDATE users SET phone = ? WHERE id = ? ''', (newphone, userid



126



# The user's phone is not updated



127



db.rollback()



128 129



'''



130



Please remember to always call commit to save the changes.



131



If you close the connection using close or the connection to the file is lost



132



(maybe the program finishes unexpectedly), not committed changes will be lost.



133



'''



134 135 136 137



### Exception Handling:



138



try:



139



db = sqlite3.connect('db.sqlite3')



140



cursor = db.cursor()



141



cursor.execute('''CREATE TABLE IF NOT EXISTS



142



users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT, pa



143 144



db.commit() except Exception as e:



145



# This is called a catch-all clause.



146



# This is used here only as an example.



147



# In a real application you should catch a specific exception such as IntegrityError



148 149



# Roll back any change if something goes wrong



150



db.rollback()



151



raise e



152



finally:



153



db.close()



154 155



### SQLite Row Factory and Data Types



156



'''



157



The following table shows the relation between SQLite datatypes and Python datatypes:



158 159



None type is converted to NULL



160



int type is converted to INTEGER



161



float type is converted to REAL



162



str type is converted to TEXT



163



bytes type is converted to BLOB



164



'''



165



https://gist.github.com/7aman/23678d233fa50900cc70ebfef2fd1f94



19/01/21, 2:46 PM Page 4 of 6



166



# The row factory class sqlite3.Row is used to access the columns of a query by name inst



167



db = sqlite3.connect('db.sqlite3')



168



db.row_factory = sqlite3.Row



169



cursor = db.cursor()



170



cursor.execute('''SELECT name, email, phone FROM users''')



171



for row in cursor:



172



# row['name'] returns the name column in the query, row['email'] returns email column



173



print('{0} -> {1}, {2}'.format(row['name'], row['email'], row['phone']))



174



db.close()



175 176 177 178



########



179



# DROP #



180



########



181



db = sqlite3.connect('db.sqlite3')



182



cursor = db.cursor()



183



cursor.execute('''DROP TABLE users''')



184



db.commit()



185 186



# When we are done working with the DB we need to close the connection:



187



db.close()



XtremePwnership commented on Sep 20, 2020 Thanks a TON! :D



7aman commented on Sep 20, 2020



Owner



Author



Thanks a TON! :D you're welcome



AmirHosein-Gharaati commented on Oct 11, 2020 Thank you! Also you can give an example with INSERT OR IGNORE INTO.



https://gist.github.com/7aman/23678d233fa50900cc70ebfef2fd1f94



19/01/21, 2:46 PM Page 5 of 6



DarrellJonsson commented on Dec 20, 2020 Thanks!



https://gist.github.com/7aman/23678d233fa50900cc70ebfef2fd1f94



19/01/21, 2:46 PM Page 6 of 6