python记录

标签：INSERT execute cur 记录 python dict values my

1,要注意 Python 是区分大小写的
2,此外，你需要确保每一行的第一个字符前面都没有任何空格或制表格
3,运行 help('len') 命令——这将显示出有关len 函数的帮助,小贴士：按下 q 键可以退出帮助
4,按ctrl+z或quit()退出控制台运行
5，注释是任何存在于 # 号右侧的文字
6，数字主要分为两种类型——整数（Integers）与浮点数（Floats）
没有单独的 long 类型。 int 类型可以指任何大小的整数。
有关浮点数（Floating Point Numbers，在英文中也会简写为 floats ）的例子是 3.23 或
52.3E-4 。其中， E 表示 10 的幂。在这里， 52.3E-4 表示 52.3 * 10^-4 。
字符串：
一串字符串（String）是字符（Characters）的序列（Sequence）。基本上，字符串就是一串词汇。
7，单引号
你可以使用单引号来指定字符串，例如 '将我这样框进来' 或 'Quote me on this' 。
所有引号内的空间，诸如空格与制表符，都将按原样保留
8，双引号
被双引号包括的字符串和被单引号括起的字符串其工作机制完全相同。例如 "你的名字是？" 或
"What's your name?" 。
9，三引号
你可以通过使用三个引号—— """ 或 ''' 来指定多行字符串。你可以在三引号之间自由地
使用单引号与双引号。比如下面：
'''这是一段多行字符串。这是它的第一行。
This is the second line.
"What's your name?," I asked.
He said "Bond, James Bond."
'''
10，格式化方法
age = 20
name = 'Swaroop'
print('{0} was {1} years old when he wrote this book'.format(name, age))
print('Why is {0} playing with that python?'.format(name))
我们可以通过联立字符串来达到相同的效果：name + 'is' +str(age) + 'years old'

pip install lxml
pip install requests
pip install pandas
pip install bs4
pip install tushare

connection.execute("""
INSERT INTO
{tn}
VALUES
(NULL, :col1, :col2)""".format(tn=tableName),
{"col1": text1, "col2": text2})

connection.execute("CREATE TABLE IF NOT EXISTS {tn} ({nf1} {ft1} PRIMARY KEY AUTOINCREMENT)"\
.format(tn = tableName, nf1 = "IDPK", ft1 = "INTEGER"))

# One way. If keys can be corrupted don't use.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
','.join(my_dict.keys()),
','.join(['?']*len(my_dict)))

# Another, better way. Hardcoded w/ your keys.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
','.join(my_keys),
','.join(['?']*len(my_dict)))

cur.execute(sql, tuple(my_dict.values()))

columns = ', '.join(my_dict.keys())
placeholders = ':'+', :'.join(my_dict.keys())
query = 'INSERT INTO my_table (%s) VALUES (%s)' % (columns, placeholders)
print query
cur.execute(query, my_dict)
con.commit()

values = {'title':'jack', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}
cur.execute('INSERT INTO Media (id, title, type, onchapter, chapters, status) VALUES (:id, :title, :type, :onchapter, :chapters, :status);'), values)

cur.execute('INSERT INTO Media VALUES (NULL, :title, :type, :genre, :onchapter, :chapters, :status)', values)

I would like to use a dictionary to insert values into a table, how would I do this?

import sqlite3

db = sqlite3.connect('local.db')
cur = db.cursor()

cur.execute('DROP TABLE IF EXISTS Media')

cur.execute('''CREATE TABLE IF NOT EXISTS Media(
id INTEGER PRIMARY KEY, title TEXT,
type TEXT, genre TEXT,
onchapter INTEGER, chapters INTEGER,
status TEXT
)''')

values = {'title':'jack', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}

#What would I Replace x with to allow a
#dictionary to connect to the values?
cur.execute('INSERT INTO Media VALUES (NULL, x)'), values)
cur.execute('SELECT * FROM Media')

meida = cur.fetchone()

print meida

python dictionary parameters sqlite insert
share improve this question
asked Jan 1 '13 at 5:55
Crispy
2,6401010 gold badges3434 silver badges5454 bronze badges

The question here is: Why would you like to do this? What are you trying to accomplish, and why do you think keeping two completely differently-specified things (a SQL table and a dict set of keys) in sync is the right way to do it? – abarnert Jan 1 '13 at 7:07
1
Don't know what the original author wanted, but I found the question because I'm generating the create table from the structure of an XML file, then when I go to insert the rows, I have the attrs and vals in a dict and I know the db will have them, but they're never static. – eichin Aug 27 '14 at 6:01
@eichin I know this is a little late, but... If you're just preloading a database from XML to use more statically later, I'd probably build a load script instead of a bunch of inserts; if you're actually accessing the database with dynamic columns all the time, you should seriously consider a different kind of store than a SQL relational database. What you're doing may be the best thing in your case, but it usually isn't, so SQL makes it hard. – abarnert Mar 9 '16 at 9:24

add a comment
9 Answers
Active
Oldest
Votes
36

If you're trying to use a dict to specify both the column names and the values, you can't do that, at least not directly.

That's really inherent in SQL. If you don't specify the list of column names, you have to specify them in CREATE TABLE order—which you can't do with a dict, because a dict has no order. If you really wanted to, of course, you could use a collections.OrderedDict, make sure it's in the right order, and then just pass values.values(). But at that point, why not just have a list (or tuple) in the first place? If you're absolutely sure you've got all the values, in the right order, and you want to refer to them by order rather than by name, what you have is a list, not a dict.

And there's no way to bind column names (or table names, etc.) in SQL, just values.

You can, of course, generate the SQL statement dynamically. For example:

columns = ', '.join(values.keys())
placeholders = ', '.join('?' * len(values))
sql = 'INSERT INTO Media ({}) VALUES ({})'.format(columns, placeholders)
cur.execute(sql, values.values())

However, this is almost always a bad idea. This really isn't much better than generating and execing dynamic Python code. And you've just lost all of the benefits of using placeholders in the first place—primarily protection from SQL injection attacks, but also less important things like faster compilation, better caching, etc. within the DB engine.

It's probably better to step back and look at this problem from a higher level. For example, maybe you didn't really want a static list of properties, but rather a name-value MediaProperties table? Or, alternatively, maybe you want some kind of document-based storage (whether that's a high-powered nosql system, or just a bunch of JSON or YAML objects stored in a shelve)?

An alternative using named placeholders:

share improve this answer
edited Jun 19 '13 at 11:28
Ben Reich
14.9k22 gold badges3232 silver badges5454 bronze badges
answered Jan 1 '13 at 6:59
abarnert
292k3232 gold badges457457 silver badges549549 bronze badges

@oche: "Not tested but the code seems wrong. Inserting keys into the table twice (for the placeholder) looks like a slight mistake." No, the execute call replaces each named placeholder by looking up its name as a key in my_dict. Therefore, it definitely should be keys, not values. Imagine that my_dict = {'spam': 'eggs'}. If you put a placeholder :eggs in the SQL statement, that wouldn't be found in my_dict; you need :spam. – abarnert May 20 '15 at 21:26
Placeholders automatically do correct escaping, whereas if you build the query yourself, you need to code up the special character escape logic. – dietbuddha Oct 7 '15 at 20:24
1
Placeholders don't do correct escaping, even automatically. They simply don't do escaping, because they're not textually inserted into a SQL statement. – Jürgen A. Erhard Feb 17 '16 at 6:16
@JürgenA.Erhard Technically, under the covers, some SQL libraries actually do handle placeholders by just escaping them into the string… But users don't have to know that; each SQL library does the best thing with placeholders for its backend, and does it correctly, so +1 on your comment. – abarnert Mar 9 '16 at 9:20
Works like a charm! ty – user2925795 Aug 9 '17 at 21:20

show 2 more comments
14

There is a solution for using dictionaries. First, the sql-statement

INSERT INTO Media VALUES (NULL, 'x');

would not work, as it assumes you are referring to all columns, in the order they are defined in the CREATE TABLE statement, as abarnert stated. (See SQLite INSERT.)

Once you have fixed it by specifying the columns, you can use named placeholders to insert data. The advantage of this is that is safely escapes key-characters, so you do not have to worry. From the Python sqlite-documentation:

share improve this answer
answered May 22 '13 at 17:41
MrGumble
3,91211 gold badge1212 silver badges2828 bronze badges

Hey I made an edit, but caught another error afterwards and I can't change enough to make another haha. At the end of your statement you have );)' which execute() will read as you trying to make multiple statements. Remove the extra ) and all is well. :) – CodeSpent Nov 20 '18 at 3:36

add a comment
11

You could use named parameters:

cur.execute('INSERT INTO Media VALUES (NULL, :title, :type, :genre, :onchapter, :chapters, :status)', values)

This still depends on the column order in the INSERT statement (those : are only used as keys in the values dict) but it at least gets away from having to order the values on the python side, plus you can have other things in values that are ignored here; if you're pulling what's in the dict apart to store it in multiple tables, that can be useful.

If you still want to avoid duplicating the names, you could extract them from an sqlite3.Row result object, or from cur.description, after doing a dummy query; it may be saner to keep them around in python form near wherever you do your CREATE TABLE.
share improve this answer
answered Jan 15 '15 at 1:54
eichin
17522 silver badges66 bronze badges
add a comment
3

Here's a more generic way with the benefit of escaping:

# One way. If keys can be corrupted don't use.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
','.join(my_dict.keys()),
','.join(['?']*len(my_dict)))

# Another, better way. Hardcoded w/ your keys.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
','.join(my_keys),
','.join(['?']*len(my_dict)))

cur.execute(sql, tuple(my_dict.values()))

share improve this answer
answered Jun 6 '19 at 16:52
keithpjolley
1,3841111 silver badges1515 bronze badges
add a comment
1

key_lst = ('status', 'title', 'chapters', 'onchapter', 'genre', 'type')
cur.execute('INSERT INTO Media (status,title,chapters,onchapter,genre,type) VALUES ' +
'(?,?,?,?,?,?);)',tuple(values[k] for k in key_lst))

I would like to use a dictionary to insert values into a table, how would I do this?

import sqlite3

db = sqlite3.connect('local.db')
cur = db.cursor()

cur.execute('DROP TABLE IF EXISTS Media')

cur.execute('''CREATE TABLE IF NOT EXISTS Media(
id INTEGER PRIMARY KEY, title TEXT,
type TEXT, genre TEXT,
onchapter INTEGER, chapters INTEGER,
status TEXT
)''')

values = {'title':'jack', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}

#What would I Replace x with to allow a
#dictionary to connect to the values?
cur.execute('INSERT INTO Media VALUES (NULL, x)'), values)
cur.execute('SELECT * FROM Media')

meida = cur.fetchone()

print meida

python dictionary parameters sqlite insert
share improve this question
asked Jan 1 '13 at 5:55
Crispy
2,6401010 gold badges3434 silver badges5454 bronze badges

add a comment
9 Answers
Active
Oldest
Votes
36

If you're trying to use a dict to specify both the column names and the values, you can't do that, at least not directly.

And there's no way to bind column names (or table names, etc.) in SQL, just values.

You can, of course, generate the SQL statement dynamically. For example:

columns = ', '.join(values.keys())
placeholders = ', '.join('?' * len(values))
sql = 'INSERT INTO Media ({}) VALUES ({})'.format(columns, placeholders)
cur.execute(sql, values.values())

An alternative using named placeholders:

show 2 more comments
14

There is a solution for using dictionaries. First, the sql-statement

INSERT INTO Media VALUES (NULL, 'x');

would not work, as it assumes you are referring to all columns, in the order they are defined in the CREATE TABLE statement, as abarnert stated. (See SQLite INSERT.)

share improve this answer
answered May 22 '13 at 17:41
MrGumble
3,91211 gold badge1212 silver badges2828 bronze badges

add a comment
11

You could use named parameters:

cur.execute('INSERT INTO Media VALUES (NULL, :title, :type, :genre, :onchapter, :chapters, :status)', values)

Here's a more generic way with the benefit of escaping:

# One way. If keys can be corrupted don't use.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
','.join(my_dict.keys()),
','.join(['?']*len(my_dict)))

# Another, better way. Hardcoded w/ your keys.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
','.join(my_keys),
','.join(['?']*len(my_dict)))

cur.execute(sql, tuple(my_dict.values()))

share improve this answer
answered Jun 6 '19 at 16:52
keithpjolley
1,3841111 silver badges1515 bronze badges
add a comment
1

Do your escaping right.

You probably also need a commit call in there someplace.
share improve this answer
answered Jan 1 '13 at 7:00
tacaswell
67.2k1313 gold badges177177 silver badges174174 bronze badges

Why not use the key_lst to specify the column order instead of retyping it? cur.execute('INSERT INTO Media ({}) VALUES ();'.format(','.join(key_lst), ','.join(['?'] * len(key_lst))), tuple(values[k] for k in key_lst)) – MrGumble May 22 '13 at 17:45
@MrGumble because you are still letting possibly user supplied data go into the sql statement with out escaping it. And I think you are missing a {} in the format statement. You should include this suggestion in your answer if you like it. – tacaswell May 22 '13 at 18:11

add a comment
1

Super late to this, but figured I would add my own answer. Not an expert, but something I found that works.

There are issues with preserving order when using a dictionary, which other users have stated, but you could do the following:

# We're going to use a list of dictionaries, since that's what I'm having to use in my problem
input_list = [{'a' : 1 , 'b' : 2 , 'c' : 3} , {'a' : 14 , 'b' : '' , 'c' : 43}]
for i in input_list:
# I recommend putting this inside a function, this way if this
# Evaluates to None at the end of the loop, you can exit without doing an insert
if i :
input_dict = i
else:
input_dict = None
continue
# I am noting here that in my case, I know all columns will exist.
# If you're not sure, you'll have to get all possible columns first.

keylist = list(input_dict.keys())
vallist = list(input_dict.values())

query = 'INSERT INTO example (' +','.join( ['[' + i + ']' for i in keylist]) + ') VALUES (' + ','.join(['?' for i in vallist]) + ')'

items_to_insert = list(tuple(x.get(i , '') for i in keylist) for x in input_list)
# Making sure to preserve insert order.

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.executemany(query , items_to_insert)
conn.commit()

share improve this answer
edited Sep 10 '19 at 14:52
answered Sep 10 '19 at 14:45
a7xcarter
5677 bronze badges
add a comment
1

dictionary = {'id':123, 'name': 'Abc', 'address':'xyz'}
query = "insert into table_name " + str(tuple(dictionary.keys())) + " values" + str(tuple(dictionary.values())) + ";"
cursor.execute(query)
insert into table_name ('id', 'name', 'address') values(123, 'Abc', 'xyz');

import sqlite3
db = sqlite3.connect('local.db')
cur = db.cursor()

cur.execute('DROP TABLE IF EXISTS Media')

cur.execute('''CREATE TABLE IF NOT EXISTS Media(
id INTEGER PRIMARY KEY, title TEXT,
type TEXT, genre TEXT,
onchapter INTEGER, chapters INTEGER,
status TEXT
)''')

values = {'title':'jack', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}

#What would I Replace x with to allow a
#dictionary to connect to the values?
#cur.execute('INSERT INTO Media VALUES (NULL, x)'), values)
# Added code.
cur.execute('SELECT * FROM Media')
colnames = cur.description
list = [row[0] for row in cur.description]
new_list = [values[i] for i in list if i in values.keys()]
sql = "INSERT INTO Media VALUES ( NULL, "
qmarks = ', '.join('?' * len(values))
sql += qmarks + ")"
cur.execute(sql, new_list)
#db.commit() #<-Might be important.
cur.execute('SELECT * FROM Media')
media = cur.fetchone()
print (media)

pyinstaller -F retest.py

pip list > d:\piplist.txt
Package Version
---------------- ---------
altgraph 0.17
beautifulsoup4 4.9.1
bs4 0.0.1
certifi 2020.6.20
chardet 3.0.4
chinesecalendar 1.4.0
cycler 0.10.0
future 0.18.2
idna 2.10
kiwisolver 1.2.0
lxml 4.5.2
matplotlib 3.3.0
numpy 1.19.1
pandas 1.0.5
pefile 2019.4.18
Pillow 7.2.0
pip 20.1.1
PyInstaller 3.6
pyparsing 2.4.7
python-dateutil 2.8.1
pytz 2020.1
pywin32-ctypes 0.2.0
requests 2.24.0
setuptools 41.2.0
simplejson 3.17.2
six 1.15.0
soupsieve 2.0.1
tushare 1.2.60
urllib3 1.25.10
websocket-client 0.57.0

标签：INSERT,execute,cur,记录,python,dict,values,my
From： https://www.cnblogs.com/zhangmo/p/17387091.html

相关文章

赞助商

阅读排行