在python中读取外部sql脚本
在python中读取外部sql脚本
提问于 2013-10-20 09:21:27
我正在学习如何在python中执行SQL (我知道SQL，不知道Python)。
我有一个外部sql文件。它创建数据并插入到三个表'Zookeeper'，'Handles'，'Animal‘中。
然后，我有一系列的查询来运行这些表。以下查询位于我加载的zookeeper.sql文件中，该文件位于python脚本的顶部。前两个示例如下：
--1.1
SELECT ANAME,zookeepid
FROM ANIMAL, HANDLES
WHERE AID=ANIMALID;--1.2
SELECT ZNAME, SUM(TIMETOFEED)
FROM ZOOKEEPER, ANIMAL, HANDLES
WHERE AID=ANIMALID AND ZOOKEEPID=ZID
GROUP BY zookeeper.zname;这些都在SQL中执行得很好。现在我需要在Python中执行它们。我已经给出并完成了在文件中读取的代码。然后执行循环中的所有查询。
1.1和1.2是我感到困惑的地方。我相信在循环中，这是我应该放入一些东西来运行第一个和第二个查询的行。
结果= c.execute("SELECT * FROM %s;“% table)；
但是什么呢？我想我遗漏了一些非常明显的东西。我想让我失望的是% table。在查询1.1和1.2中，我不是在创建表，而是在寻找查询结果。
下面是我的整个python代码。
import sqlite3
from sqlite3 import OperationalError
conn = sqlite3.connect('csc455_HW3.db')
c = conn.cursor()
# Open and read the file as a single buffer
fd = open('ZooDatabase.sql', 'r')
sqlFile = fd.read()
fd.close()
# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')
# Execute every command from the input file
for command in sqlCommands:
# This will skip and report errors
# For example, if the tables do not yet exist, this will skip over
# the DROP TABLE commands
try:
c.execute(command)
except OperationalError, msg:
print "Command skipped: ", msg
# For each of the 3 tables, query the database and print the contents
for table in ['ZooKeeper', 'Animal', 'Handles']:
**# Plug in the name of the table into SELECT * query
result = c.execute("SELECT * FROM %s;" % table);**
# Get all rows.
rows = result.fetchall();
# \n represents an end-of-line
print "\n--- TABLE ", table, "\n"
# This will print the name of the columns, padding each name up
# to 22 characters. Note that comma at the end prevents new lines
for desc in result.description:
print desc[0].rjust(22, ' '),
# End the line with column names
print ""
for row in rows:
for value in row:
# Print each value, padding it up with ' ' to 22 characters on the right
print str(value).rjust(22, ' '),
# End the values from the row
print ""
c.close()
conn.close()回答 3
Stack Overflow用户
发布于 2013-10-20 10:12:57
您的代码已经包含了一种执行指定sql文件中所有语句的漂亮方法
# Open and read the file as a single buffer
fd = open('ZooDatabase.sql', 'r')
sqlFile = fd.read()
fd.close()
# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')
# Execute every command from the input file
for command in sqlCommands:
# This will skip and report errors
# For example, if the tables do not yet exist, this will skip over
# the DROP TABLE commands
try:
c.execute(command)
except OperationalError, msg:
print("Command skipped: ", msg)将其封装在一个函数中，您可以重用它。
def executeScriptsFromFile(filename):
# Open and read the file as a single buffer
fd = open(filename, 'r')
sqlFile = fd.read()
fd.close()
# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')
# Execute every command from the input file
for command in sqlCommands:
# This will skip and report errors
# For example, if the tables do not yet exist, this will skip over
# the DROP TABLE commands
try:
c.execute(command)
except OperationalError, msg:
print("Command skipped: ", msg)使用它
executeScriptsFromFile('zookeeper.sql')你说你很迷惑
result = c.execute("SELECT * FROM %s;" % table);在Python中，您可以使用一种称为字符串格式化的方法向字符串中添加内容。
你有一个带有%s的字符串"Some string with %s"，这是其他东西的占位符。要替换占位符，请在字符串后添加% ("what you To replace it with
例如：
a = "Hi, my name is %s and I have a %s hat" % ("Azeirah", "cool")
print(a)
>>> Hi, my name is Azeirah and I have a Cool hat这是一个幼稚的例子，但它应该是清晰的。
现在，什么？
result = c.execute("SELECT * FROM %s;" % table);意思是，它将%s替换为表变量的值。
(创建于)
for table in ['ZooKeeper', 'Animal', 'Handles']:
# for loop example
for fruit in ["apple", "pear", "orange"]:
print(fruit)
>>> apple
>>> pear
>>> orange如果你还有其他问题，尽管来找我。
Stack Overflow用户
发布于 2020-09-02 15:08:20
在python中将外部脚本读入sqlite数据库的一种非常简单的方法是使用executescript()。
import sqlite3
conn = sqlite3.connect('csc455_HW3.db')
with open('ZooDatabase.sql', 'r') as sql_file:
conn.executescript(sql_file.read())
conn.close()Stack Overflow用户
发布于 2021-07-25 05:58:27
首先确保表存在，如果不存在，则创建一个表，然后按照以下步骤操作。
import sqlite3
from sqlite3 import OperationalError
conn = sqlite3.connect('Client_DB.db')
c = conn.cursor()
def execute_sqlfile(filename):
c.execute("CREATE TABLE clients_parameters (adress text, ie text)")
#
fd = open(filename, 'r')
sqlFile = fd.readlines()
fd.close()
lvalues = [tuple(v.split(';')) for v in sqlFile[1:] ]
try:
#print(command)
c.executemany("INSERT INTO clients_parameters VALUES (?, ?)", lvalues)
except OperationalError as msg:
print ("Command skipped: ", msg)
execute_sqlfile('clients.sql')
print(c.rowcount)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19472922
复制相关文章
相似问题