Call a variable within a python sqlite execute function. - python

I am trying to create several tables through a loop. To do this I will need a table name which will change when creating each table. I am trying to do this by having a list that adds a character onto the table name.
I have then tried to include the variable inside the execution of an sqlite command.
However I have been getting this error:
sqlite3.OperationalError: near "TABLEformI": syntax error
Here is my code:
def createFormGroups(dbFileDir, csvFileDir):
groupNumber = input('Enter the number of groups you want')
conn = sqlite3.connect(dbFileDir)
cur = conn.cursor()
currentTable = 0
while currentTable != groupNumber:
currentTable = currentTable + 1
nameGroupList = ['form']
nameGroupList.append('I')
nameGroup = ''.join(nameGroupList)
createGroupTable = cur.execute('''CREATE TABLE''' + nameGroup + '''(ID integer PRIMARY KEY, Gender text,Postcode text,SEN text,Rank integer,FirstChoice text,SecondChoice text,ThirdChoice text,Avoid1 text,Avoid2 text)''')
conn.commit()
conn.close()
Can you spot any problems within my code.

You want formI, formII, formIII as the table names?
I would suggest:
nameGroup = 'form' + 'I' * currentTable
Now don't ask for those to be normalized roman numerals.

Related

How to check if Record exists

I want to able to check if a record exists in the DB with a specific 'ID' and create one if it does not have one( i.e, Doesnt exist) Like so...
I would also like to fetch "Num" For a specific record after it has been updated.
import sqlite3 as lite
db = lite.connect("test.db")
id_to_be_added = "123456789101112"
db.execute("CREATE TABLE USERS (ID TEXT, NUM INT)")
Query = "{ SOMETHING IN SQL }" # This returns either True or False
if Query:
db.execute("UPDATE USERS SET NUM = NUM + 1 WHERE ID = {};".format(id_to_be_added))
else:
db.execute("INSERT INTO USERS ({}, 0)".format(id_to_be_added))
num_to_be_printed = db.execute("SELECT NUM FROM USERS WHERE ID = {}".format(id_to_be_added))
print("{0} has {1}").format(id_to_be_added, num_to_be_printed)
Either create a primary key and use INSERT OR REPLACE query, or use a SELECT query.
I was able to solve it using cur.fetchall()
import sqlite3 as lite
db = lite.connect(r"test.db")
id_tba = r"123456789101112"
cur = db.cursor()
cur.execute("SELECT * FROM USERS WHERE ID = {}".format(id_tba))
if len(cur.fetchall()) > 0:
db.execute("UPDATE USERS SET NUM = NUM + 1 WHERE ID = {};".format(id_tba))
else:
db.execute("INSERT INTO USERS ({}, 0)".format(id_tba))
num_to_be_printed = db.execute("SELECT NUM FROM USERS WHERE ID = {}".format(id_tba))
print("{0} has {1}").format(id_tba, num_to_be_printed)

Updating table using sqlite3 and python

I am trying to update a table titled 'stats' using sqlite3 and pyhton. The table has the structure [username, correct, total]. This is the code I'm using:
conn = sqlite3.connect(stats_db) # connect to that database (will create if it doesn't already exist)
c = conn.cursor() # make cursor into database (allows us to execute commands)
outs = ""
try:
c.execute('''CREATE TABLE stats (username text,correct int, total int);''') # run a CREATE TABLE command
outs = "database constructed"
except:
things = c.execute('''SELECT * FROM stats;''').fetchall()
output_list = []
for x in things:
output_list.append(x)
new_correct = output_list[0][1] + int(correct)
new_total = output_list[0][2] + 1
c.execute('''UPDATE stats SET correct = (?) and total = (?) where username = (?);''',(new_correct, new_total, username))
things = c.execute('''SELECT * FROM stats;''').fetchall() #
outs = "Things:\n"
output_list = []
for x in things:
output_list.append(x)
outs += str(output_list)
conn.commit() # commit commands
conn.close() # close connection to database
return outs
However, the values in the table never get updated. new_correct and new_total are returning correctly. Also, is there a way to update without first calling the previous values? I just need to increment total by 1 and correct by either 0 or 1 depending on what the input of correct is?
Resolved, I had to change the and in my update command to a comma. Now it reads:
c.execute('''UPDATE stats SET correct = ?, total = ? where username = ?;''',(new_correct, new_total, username))

How to store results from SQL query and use it?

I am trying to see whether the type is either a the letter "T" or between number 1-6 for the specific data entry found with name and password.
sql = 'SELECT type FROM table name WHERE name = "{}" AND password = "{}"'.format(username, password)
and then in psedocode i need something like:
if type =< 5:
int()
elif type = "T"
string()
I am using python 2.7
Here is a full script that will query the mysql DB, and use your above-mentioned logic to print the values. I've included the python code as well as the sample database code for this test case. Let me know if you have any questions.
Python
import pymysql
connection = pymysql.connect(user='username', passwd='password',
host='localhost',
database='database')
cursor = connection.cursor()
NAME = 'Person_A'
PASSWORD = 'Password_A'
query = ("SELECT * FROM My_TABLE WHERE NAME = '%(1)s' AND PASSWORD = '%(2)s';" % {"1" : NAME, "2" : PASSWORD})
cursor.execute(query)
for item in cursor:
type = item[0]
if type.isdigit():
if int(type) <6:
print('Type is a number less than 6')
else:
print('Type is a number but not less than 6')
else:
if type == 'T':
print('Type is a string == T')
else:
print('Type is a string but not the letter T')
MYSQL
CREATE TABLE MY_TABLE (TYPE VARCHAR(255), NAME VARCHAR(255), PASSWORD VARCHAR(255));
INSERT INTO MY_TABLE VALUES ('T','Person_A','Password_A'),('4','Person_A','Password_A'),('7','Person_B','Password_B'),('t','Person_C','Password_C');

SQLite/python - Adding columns with data inside while loop

I'm trying to build a SQLite database from scraped text. Each row in the database corresponds to a string taken from a list, and for every loop another column is created and populated with new string data.
conn = sqlite3.connect('data.sqlite')
cur = conn.cursor()
cur.executescript('''
DROP TABLE IF EXISTS Data;
CREATE TABLE Data(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
words text)''')
while True
url = 'www.xyz.com'
if url == "break": break
#parse - find tag of interest
html = urllib.request.urlopen(url)
p_s = BeautifulSoup(html,'html.parser')
words = str(p_s.findAll('p',{'id':'p-5'}))
words = strip_tags(words)
words = pd.DataFrame(words)
col_number = col_number + 1
col_name = ('Group', col_number)
cur.execute('''ALTER TABLE Data ADD ? TEXT''', (col_name,))
for i,j in words.iterrows():
cur.execute('''INSERT OR IGNORE INTO Data (col_name)
VALUES (?)''',(j))
conn.commit()
When I run this code I get:
sqlite3.Operational.Error : near "?": syntax error
Where am I going wrong? Thanks, and I apologize for my sloppy code, I'm new to Python!
Change to:
cur.execute('''ALTER TABLE Data ADD {} TEXT'''.format('Group ' + str(col_number)))
for i,j in words.iterrows():
cur.execute('''INSERT OR IGNORE INTO Data ({})
VALUES (?)'''.format(col_name), (j,))
conn.commit()
or
cur.execute('''ALTER TABLE Data ADD {} TEXT'''.format('Group ' + str(col_number)))
for i,j in words.iterrows():
cur.execute('''INSERT OR IGNORE INTO Data {}
VALUES (?)'''.format(col_name), (j,))
conn.commit()
one of those should work

How do I insert variables in the UPDATE Statement sqlite3 with Python?

If I want to insert a variable value in sqlite3 with python you code the following statement. For example:
import sqlite3
import random
conn = sqlite3.connect('testing.db')
c = conn.cursor()
c.execute("CREATE TABLE IF NOT EXISTS testing(Name TEXT, Telephone TEXT, ID REAL)")
var1 = input("Value:")
var2 = input("Value:")
var3 = random.randint(1,20)
c.execute("INSERT INTO testing VALUES(?, ?, ?)", (var1, var2, var3))
conn.commit()
conn.close()
What if I want to do the same with the UPDATE statement. I tried this and is giving me error:
column = input("Column to update:")
update_user = input("Value to update:")
field_name = input("Where field name equal to:")
value = input("Value")
c.execute("UPDATE testing SET ? = ? WHERE ? = ?", (column, update_user, field_name, value))
conn.commit()
And this is the error that I get:
sqlite3.OperationalError: near "?": syntax error
This is actually a common problem, in your update query, you cannot parameterize the column names. In other words:
UPDATE testing SET ? = ? WHERE ? = ?
THIS^ THIS^
cannot be query placeholders and you have to insert them the usual way - via string formatting:
c.execute("""
UPDATE
testing
SET
{0} = ?
WHERE
{1} = ?""".format(column, field_name), (update_user, value))
Though, you should be careful about properly sanitizing, validating the column and field_name values to prevent SQL injection attacks.

Resources