Date
Jul. 21st, 2024
 
2024年 6月 16日

Post: Python sqlite Cheatsheet

Python sqlite Cheatsheet

Published 08:08 Aug 02, 2020.

Created by @ezra. Categorized in #Programming, and tagged as #Cheatsheet.

Source format: Markdown

Table of Content

Sqlite - Python Driver

import sqlite3
from sqlite3 import Error

try:
    conn = sqlite3.connect('example.db')

    # Once you have a Connection, you can create a Cursor object 
    # and call its execute() method to perform SQL commands:
    cur = con.cursor()

    # Create table
    cur.execute('''CREATE TABLE stocks
                   (date text, trans text, symbol text, qty real, price real)''')

    # Insert a row of data
    cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

    # Save (commit) the changes
    con.commit()
except Error as e:
    print(e)
finally:
    if conn:
        # We can also close the connection if we are done with it.
        # Just be sure any changes have been committed or they will be lost.
        conn.close()
````
Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack
```python
# Never do this -- insecure!
symbol = 'RHAT'
cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

Alternative to Python Variables

# ...

# This is the qmark style:
cur.execute("insert into lang values (?, ?)", ("C", 1972))

# The qmark style used with executemany():
lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)

# And this is the named style:
cur.execute("select * from lang where first_appeared=:year", {"year": 1972})
print(cur.fetchall())

# ...

An SQL statement may use one of two kinds of placeholders: question marks (qmark style) or named placeholders (named style)

Pinned Message
HOTODOGO
I'm looking for a SOFTWARE PROJECT DIRECTOR / SOFTWARE R&D DIRECTOR position in a fresh and dynamic company. I would like to gain the right experience and extend my skills while working in great teams and big projects.
Feel free to contact me.
For more information, please view online résumé or download PDF
本人正在寻求任职 软件项目经理 / 软件技术经理 岗位的机会, 希望加⼊某个新鲜⽽充满活⼒的公司。
如有意向请随时 与我联系
更多信息请 查阅在线简历下载 PDF