Python sqlite3，在.format的帮助下从列表中创建表列 [英] Python sqlite3, create table columns from a list with help of .format

问题描述

我正在尝试自动创建一个相当大的 sqlite 数据库表，这些表都至少有 50 列.列名已经在不同的列表中可用.使用 .format 我几乎做到了这一点.唯一未解决的问题是从名称列表的长度中预先确定{}"的占位符数量.请参阅下面的代码示例.

I am trying to create automatically a rather large sqlite database tables which all have at least 50 columns. The column names are already available in different lists. Using the .format I almost did this. The only open issue is the predetermination of the number of placeholders for "{}" from the length of name's list. Please see the code example below.

import sqlite3

db_path = "//Some path/"
sqlite_file = 'index_db.sqlite'

conn = sqlite3.connect(db_path + sqlite_file)
c = conn.cursor()

db_columns=['c1','c2','c3','c4','c5']

#This code is working
c.execute("create table my_table1 ({}, {}, {}, {}, {})" .format(*db_columns))
#This code doesn't work
c.execute("create table my_table2 (" + ("{}, " * 5)[:-2] + ")" .format(*db_columns))
#Following error appears
OperationalError: unrecognized token: "{"

#--> This even that the curly brackets are the same as in my_table1 
print("create table my_table2 (" + ("{}, " * 5)[:-2] + ")") 
#Output: create table my_table2 ({}, {}, {}, {}, {})

c.execute("INSERT INTO my_table1 VALUES (?,?,?,?,?)", (11, 111, 111, 1111, 11111))

conn.commit()
c.close
conn.close()

有没有办法解决 my_table2 的问题?或者有没有更好的方法从列表中动态创建列名?

Is there a way to resolve that issue for my_table2? Or is there a better way to create the column names dynamically from a list?

附言这是一个内部数据库，因此我不担心由于动态使用变量作为名称而导致的安全问题.

P.s. This is an internal database so I don't have any concerns regarding security issues due to using variables as names dynamically.

提前致谢！帖木儿

推荐答案

免责声明:

不要使用字符串连接来构建 SQL 字符串 - 请参见 f.e.http://bobby-tables.com/python 了解如何使用参数化查询避免注入.

do not use string concattenation to build SQL-strings - see f.e. http://bobby-tables.com/python for how to avoid injection by using parametrized queries.

根据这个旧帖子:sqlite中的变量表名你不能使用用于创建表/列名的普通"参数化查询.

According to this old post: Variable table name in sqlite you can not use "normal" parametrized queries to create a table / columnnames.

您可以预先格式化您的 createstatement:

You can pre-format your createstatement though:

def scrub(table_name):
    # attributation: https://stackoverflow.com/a/3247553/7505395
    return ''.join( chr for chr in table_name if chr.isalnum() )

def createCreateStatement(tableName, columns):
    return f"create table {scrub(tableName)} ({columns[0]}" + (
            ",{} "*(len(columns)-1)).format(*map(scrub,columns[1:])) + ")"

tabName = "demo"
colNames = ["one", "two", "three", "dont do this"]

print(createCreateStatement(tabName, colNames))

输出:

create table demo (one,two ,three ,dontdothis )

<小时>

scrub 方法取自 Donald Miner 的回答 - 如果你支持他 :)喜欢

---

The scrub method is taken from Donald Miner's answer - upvote him :) if you like

这篇关于Python sqlite3，在.format的帮助下从列表中创建表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！