blocks|key|3238867|text|sqlite3命令行shell允许使用命令行参数执行一些设置，因此您可以简单地为每个DB文件中的表执行一个简单的SELECT+*：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3238868|for+%25%25a+in+(*.db)+do+sqlite3+-csv+-header+"%25%25a"+"select+*+from+TableName"+>+%25%25~na.csv|code-block|syntax|javascript|3238869|(如果这不是批处理文件的一部分，而是直接从命令行运行，则必须将%25%25替换为%25。)|3238870|entityMap^0|0|7|1K|8|0|0|V|2|10|1|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

The <code>sqlite3</code> command-line shell allows some settings to be done with command-line arguments, so you can simply execute a simple <code>SELECT *</code> for the table in each DB file:

<pre><code>for %%a in (*.db) do sqlite3 -csv -header "%%a" "select * from TableName" &gt; %%~na.csv
</code></pre>

(When this is not part of a batch file but run directly from the command line, you must replace <code>%%</code> with <code>%</code>.)

blocks|key|3132773|text|我创建了一个脚本，批处理将当前目录中的所有db-sqlite文件转换为CSV，称为“sqlite2csv”。它输出每个db-sqlite的每个表作为CSV文件，所以如果您有10个文件和3个表，您将得到30个CSV文件。希望它至少能作为你自己的脚本的起点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3132774|#!/bin/bash

#+USAGE+EXAMPLES+:
#+sqlite2csv
#+-+Will+loop+all+sqlite+files+in+the+current+directory,+take+the+tables+of
#+++each+of+these+sqlite+files,+and+generate+a+CSV+file+per+table.
#+++E.g.+If+there+are+10+sqlite+files+with+3+tables+each,+it+will+generate
#++++++++30+CSV+output+files,+each+containing+the+data+of+one+table.
#+++The+naming+of+the+generated+CSV+files+take+from+the+original+sqlite
#+++file+name,+prepended+with+the+name+of+the+table.

#+check+for+dependencies
if+!+type+"sqlite3"+>+/dev/null;+then
++++echo+"[ERROR]+SQLite+binary+not+found."
++++exit+1
fi

#+define+list+of+string+tokens+that+an+SQLite+file+type+should+contain
#+the+footprint+for+SQLite+3+is+"SQLite+3.x+database"
declare+-a+list_sqlite_tok
list_sqlite_tok%2B=(+"SQLite"+)
#list_sqlite_tok%2B=(+"3.x"+)
list_sqlite_tok%2B=(+"database"+)

#+get+a+lis+tof+only+files+in+current+path
list_files=(+$(find+.+-maxdepth+1+-type+f)+)

#+loop+the+list+of+files
for+f+in+${!list_files[@]};+do
++++#+get+current+file
++++curr_fname=${list_files[$f]}
++++#+get+file+type+result
++++curr_ftype=$(file+-e+apptype+-e+ascii+-e+encoding+-e+tokens+-e+cdf+-e+compress+-e+elf+-e+tar+$curr_fname)
++++#+loop+through+necessary+token+and+if+one+is+not+found+then+skip+this+file
++++curr_isqlite=0
++++for+t+in+${!list_sqlite_tok[@]};+do
++++++++curr_tok=${list_sqlite_tok[$t]}
++++++++#+check+if+'curr_ftype'+contains+'curr_tok'
++++++++if+[[+$curr_ftype+=~+$curr_tok+]];+then
++++++++++++curr_isqlite=1
++++++++else
++++++++++++curr_isqlite=0
++++++++++++break
++++++++fi
++++done
++++#+test+if+curr+file+was+sqlite
++++if+((+!+$curr_isqlite+));+then
++++++++#+if+not,+do+not+continue+executung+rest+of+script
++++++++continue
++++fi
++++#+print+sqlite+filename
++++echo+"[INFO]+Found+SQLite+file+$curr_fname,+exporting+tables..."
++++#+get+tables+of+sqlite+file+in+one+line
++++curr_tables=$(sqlite3+$curr_fname+".tables")
++++#+split+tables+line+into+an+array
++++IFS=$'+'+list_tables=($curr_tables)
++++#+loop+array+to+export+each+table
++++for+t+in+${!list_tables[@]};+do
++++++++curr_table=${list_tables[$t]}
++++++++#+strip+unsafe+characters+as+well+as+newline
++++++++curr_table=$(tr+'\n'+'+'+<<<+$curr_table)
++++++++curr_table=$(sed+-e+'s/[%5EA-Za-z0-9._-]//g'+<<<+$curr_table)+
++++++++#+temporarily+strip+'./'+from+filename
++++++++curr_fname=${curr_fname//.\//}
++++++++#+build+target+CSV+filename
++++++++printf+-v+curr_csvfname+"%25s_%25s.csv"+$curr_table+"$curr_fname"
++++++++#+put+back+'./'+to+filenames
++++++++curr_fname="./"$curr_fname
++++++++curr_csvfname="./"$curr_csvfname
++++++++#+export+current+table+to+target+CSV+file
++++++++sqlite3+-header+-csv+$curr_fname+"select+*+from+$curr_table;"+>+$curr_csvfname
++++++++#+log
++++++++echo+"[INFO]+Exported+table+$curr_table+in+file+$curr_csvfname"
++++done
done|code-block|syntax|javascript|3132775|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I made a script that batch-converts all db-sqlite files in the current directory to CSV, called 'sqlite2csv'. Well it outputs each table of each db-sqlite as a CSV file, so if you have 10 files with 3 tables each you will get 30 CSV files. Hope it helps at least as a starting point to make your own script.

<pre><code>#!/bin/bash

# USAGE EXAMPLES :
# sqlite2csv
# - Will loop all sqlite files in the current directory, take the tables of
# each of these sqlite files, and generate a CSV file per table.
# E.g. If there are 10 sqlite files with 3 tables each, it will generate
# 30 CSV output files, each containing the data of one table.
# The naming of the generated CSV files take from the original sqlite
# file name, prepended with the name of the table.

# check for dependencies
if ! type "sqlite3" &gt; /dev/null; then
 echo "[ERROR] SQLite binary not found."
 exit 1
fi

# define list of string tokens that an SQLite file type should contain
# the footprint for SQLite 3 is "SQLite 3.x database"
declare -a list_sqlite_tok
list_sqlite_tok+=( "SQLite" )
#list_sqlite_tok+=( "3.x" )
list_sqlite_tok+=( "database" )

# get a lis tof only files in current path
list_files=( $(find . -maxdepth 1 -type f) )

# loop the list of files
for f in ${!list_files[@]}; do
 # get current file
 curr_fname=${list_files[$f]}
 # get file type result
 curr_ftype=$(file -e apptype -e ascii -e encoding -e tokens -e cdf -e compress -e elf -e tar $curr_fname)
 # loop through necessary token and if one is not found then skip this file
 curr_isqlite=0
 for t in ${!list_sqlite_tok[@]}; do
 curr_tok=${list_sqlite_tok[$t]}
 # check if 'curr_ftype' contains 'curr_tok'
 if [[ $curr_ftype =~ $curr_tok ]]; then
 curr_isqlite=1
 else
 curr_isqlite=0
 break
 fi
 done
 # test if curr file was sqlite
 if (( ! $curr_isqlite )); then
 # if not, do not continue executung rest of script
 continue
 fi
 # print sqlite filename
 echo "[INFO] Found SQLite file $curr_fname, exporting tables..."
 # get tables of sqlite file in one line
 curr_tables=$(sqlite3 $curr_fname ".tables")
 # split tables line into an array
 IFS=$' ' list_tables=($curr_tables)
 # loop array to export each table
 for t in ${!list_tables[@]}; do
 curr_table=${list_tables[$t]}
 # strip unsafe characters as well as newline
 curr_table=$(tr '\n' ' ' &lt;&lt;&lt; $curr_table)
 curr_table=$(sed -e 's/[^A-Za-z0-9._-]//g' &lt;&lt;&lt; $curr_table) 
 # temporarily strip './' from filename
 curr_fname=${curr_fname//.\//}
 # build target CSV filename
 printf -v curr_csvfname "%s_%s.csv" $curr_table "$curr_fname"
 # put back './' to filenames
 curr_fname="./"$curr_fname
 curr_csvfname="./"$curr_csvfname
 # export current table to target CSV file
 sqlite3 -header -csv $curr_fname "select * from $curr_table;" &gt; $curr_csvfname
 # log
 echo "[INFO] Exported table $curr_table in file $curr_csvfname"
 done
done
</code></pre>

blocks|key|3132825|text|我准备了一个简短的python脚本，它将从多个sqlite数据库中编写一个csv文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3132826|#function+for+merging+sqlite+files+to+csv
def+convert_sqlite_to_csv(inputFolder,+ext,+tableName):
++++"""+inputFolder+-+Folder+where+sqlite+files+are+located.+
++++++++ext+-+Extension+of+your+sqlite+file+(eg.+db,+sqlite,+sqlite3+etc.)
++++++++tableName+-+table+name+from+which+you+want+to+select+the+data.
++++"""
++++csvWriter+=+csv.writer(open(inputFolder%2B'/output.csv',+'w',+newline=''))
++++for+file1+in+os.listdir(inputFolder):
++++++++if+file1.endswith('.'%2Bext):
++++++++++++conn+=+sqlite3.connect(inputFolder%2B'/'%2Bfile1)
++++++++++++cursor+=+conn.cursor()
++++++++++++cursor.execute("SELECT+*+FROM+"%2BtableName)
++++++++++++rows+=+cursor.fetchall()
++++++++++++for+row+in+rows:
++++++++++++++++csvWriter.writerow(row)
++++++++++++continue
++++++++else:
++++++++++++continue|code-block|syntax|javascript|3132827|或者在下面的github链接上找到脚本，用于转换一个文件夹中的多个文件。|3132828|python+multiple_sqlite_files_tocsv.py+-d+<inputFolder>+-e+<extension>+-t+<tableName>|3132829|将数据输出到output.csv文件。|3132830|木星笔记本和巨蟒脚本都在github上。|3132831|https://github.com/darshanz/CombineMultipleSqliteToCsv|offset|length|3132832|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|0|1I|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|11|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|13|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|15|8|@]|9|@$Q|16|R|17|1|18]]|A|$]]|$1|S|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|P]]]]

I prepared a short python script which will write a csv file from multiple sqlite databases.
<pre><code>#function for merging sqlite files to csv
def convert_sqlite_to_csv(inputFolder, ext, tableName):
 &quot;&quot;&quot; inputFolder - Folder where sqlite files are located. 
 ext - Extension of your sqlite file (eg. db, sqlite, sqlite3 etc.)
 tableName - table name from which you want to select the data.
 &quot;&quot;&quot;
 csvWriter = csv.writer(open(inputFolder+'/output.csv', 'w', newline=''))
 for file1 in os.listdir(inputFolder):
 if file1.endswith('.'+ext):
 conn = sqlite3.connect(inputFolder+'/'+file1)
 cursor = conn.cursor()
 cursor.execute(&quot;SELECT * FROM &quot;+tableName)
 rows = cursor.fetchall()
 for row in rows:
 csvWriter.writerow(row)
 continue
 else:
 continue
</code></pre>
Or find the script on github link below for converting multiple files in a folder.
<pre><code>python multiple_sqlite_files_tocsv.py -d &lt;inputFolder&gt; -e &lt;extension&gt; -t &lt;tableName&gt;
</code></pre>
will output the data to output.csv file.
Jupyter notebook and a python script are on github.
<a href="https://github.com/darshanz/CombineMultipleSqliteToCsv" rel="nofollow noreferrer">https://github.com/darshanz/CombineMultipleSqliteToCsv</a>

I have thousands of dB files that need to be converted to CSV files. This can be achieved by a simple script / batch file i.e.

<pre><code>.open "Test.db"
.mode csv
.headers on.
</code></pre>

I need the script to open the other db files which all have different names, is there a way that this can be performed as i do not want to write the above script for each db file

convert multiple DBs to CSV

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我有数千个dB文件需要转换为CSV文件。这可以通过一个简单的脚本/批处理文件来实现，即.open "Test.db".mode csv.headers on.我需要脚本来打开所有名称都不同的其他db文件，因为我不想为每个db文件编写上面的脚本，是否可以这样做？

问将多个DBs转换为CSV
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将多个DBs转换为CSVEN